working on it ...

Filters

Explore Public Snippets

Sort by

Found 9,339 snippets

    public by sjschmalfeld modified Jan 4, 2018  234  0  4  0

    SQL Duplicates

    WITH cte AS
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY (SELECT 0)) AS DuplicateRowNumber
        FROM table
    )
    SELECT FROM cte WHERE DuplicateRowNumber > 1
    
    
    or----
    
    WITH cte as
    (SELECT PEOPLE.*, Loan.LoanID, ROW_NUMBER() OVER (PARTITION BY SocialSecurityNo ORDER BY SocialSecurityNo) AS DuplicateRowNumber
    FROM people INNER JOIN LOAN ON FK_PeopleID=PeopleID WHERE ISNULL(SocialSecurityNo,'') <> '' AND SocialSecurityNo <> '--' AND SocialSecurityNo <> '000-00-0000' AND loan.Status='active' )
    
    SELECT * INTO #TEMP99 FROM CTE WHERE cte.DuplicateRowNumber > 1
    
    SELECT *,b.SocialSecurityNo FROM dbo.Loan 
    	INNER JOIN (SELECT PeopleID,SocialSecurityNo FROM PEOPLE WHERE SocialSecurityNo	 
    							IN (SELECT SocialSecurityNo FROM #temp99)) b 
    	ON B.PeopleID=LOAN.FK_PeopleID
    	WHERE LOAN.Status='ACTIVE'
    
    DROP TABLE #TEMP99  
    
    
    -- Another way
    	--GET RID OF #NewAddressCandidates ADDESSES ALEADY IN THE ADDRESS TABLE
    	DELETE FROM #NewAddressCandidates WHERE EXISTS (
    	SELECT t.[Address1] 
    				,t.Address2
    				,t.City
    				,t.[State]
    				,SUBSTRING(t.Zip,1,5) AS ZIP, 
    				SUBSTRING(t.Zip,7,4) AS ZIP4 
    		FROM #NewAddressCandidates T 
    			INTERSECT  
    		SELECT [Address1] ,[Address2],[City],[State],[Zip],ZIP4 FROM dbo.[Address])				
    		
    		
    -- Another way
    	WITH NODUP AS 
    		(SELECT  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4
    			  , row_number() OVER ( PARTITION BY  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 ORDER BY  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 ) AS nr
    		FROM    #NewAddressCandidates A)
    	DELETE FROM NODUP WHERE NR > 1;	
    	
    	SELECT * INTO  #UniqueNewAddresses  FROM #NewAddressCandidates WHERE EXISTS (
    	SELECT t.[Address1] 
    				,t.Address2
    				,t.City
    				,t.[State]
    				,SUBSTRING(t.Zip,1,5) AS ZIP, 
    				SUBSTRING(t.Zip,7,4) AS ZIP4 			
    		FROM #NewAddressCandidates T 
    			EXCEPT 
    		SELECT [Address1] ,[Address2],[City],[State],[Zip],ZIP4 FROM dbo.[Address])
    		
    		
    		
    		

    public by MuhammadAkbar modified Nov 16, 2017  33  3  3  0

    How to See/Hack saved Passwoods in Chrome

    			Many of people do not know and they do not care about own browser privacy . alot of people when they Sign up on any site and Chrome ask for save Passwords and people save passwords . actually they Do not know where their Password saved and anyone can easilly access their passwords they have saved in Chrome browser.so Today i will tell you how you can See saved passwords in Chrome and you can See/Hack saved Passwoods in Chrome. its is so easy
    
    steps. so lets start Now See/Hack saved Passwoods in ChromeView-Saved-Passwords-Chrome
    
    
    Steps below to View saves password In chrome Brower
    
                                              1: Open Chrome Brower
    
    
                                         2: Then paste this on Chrome brower
    
    "chrome://settings/passwords"
    
    when you will enter in Chrome it will be show like this 
    
    11
    
     
    
    Now Click on Any Tab it will show like this
    
    11 (1)
    
    
    when you Click on Show Button it will show the Save password in chrome
    
    you can see all password save in Chrome like this i hope this toturial will help you to find Hidden password in chrome if you need any help you and Comment below i will reply instant about See/Hack saved Passwoods in Chrome.
    						
    					http://www.watchmoviesonlineclub.com	
    					https://www.apkcheap.com
    					https://apkmirrorapp.com

    public by sjschmalfeld modified Nov 8, 2017  89  0  2  0

    Getting Min or Max value without aggregates

    SQL Server Transact SQL
    // Enter here the actual content of the snippet.
    // Select max value without using MAX()
    
    //Version 1
    SELECT *
      FROM SO.dbo.MaxNoAgg mna1
    LEFT JOIN SO.dbo.MaxNoAgg mna2 ON (mna2.salary > mna1.salary)
    WHERE mna2.mna_id IS NULL;
    
    //version 2
    SELECT age
    FROM Owner o1
    WHERE NOT EXISTS (SELECT 1 FROM Owner o2 WHERE o2.age > o1.age)            						

    public by anand modified Sep 13, 2017  43  0  1  0

    LENGTH OF CHARACTER, ORDER BY AND LIMIT

    LENGTH OF CHARACTER, ORDER BY AND LIMIT: new_gist_file.sql
    SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY CITY ASC LIMIT 1;
    
    
    SELECT * FROM CUSTOMERS
       ORDER BY (CASE ADDRESS
       WHEN 'DELHI' 	 THEN 1
       WHEN 'BHOPAL' 	 THEN 2
       WHEN 'KOTA' 	 THEN 3
       WHEN 'AHMADABAD' THEN 4
       WHEN 'MP' 	THEN 5
       ELSE 100 END) ASC, ADDRESS DESC;     ###ORDERING IN A PARTICULAR ORDER
       
       
    select * from nobel where winner like 'Sir%' order by yr desc,winner asc
    
    

    public by yourfriendcaspian modified Sep 2, 2017  81  0  1  0

    PostgreSQL - full-text search configuration

    PostgreSQL - full-text search configuration: pg_search_conf_unaccented.sql
    -- enable extensions
    -- full-text search on postgresql
    CREATE EXTENSION unaccent;
    
    -- languages supported
    CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
    ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, french_stem;
    
    CREATE TEXT SEARCH CONFIGURATION en ( COPY = english );
    ALTER TEXT SEARCH CONFIGURATION en ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, english_stem;
    
    CREATE TEXT SEARCH CONFIGURATION de ( COPY = german );
    ALTER TEXT SEARCH CONFIGURATION de ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, german_stem;
    
    CREATE TEXT SEARCH CONFIGURATION nl ( COPY = dutch );
    ALTER TEXT SEARCH CONFIGURATION nl ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, dutch_stem;
    
    CREATE TEXT SEARCH CONFIGURATION da ( COPY = danish );
    ALTER TEXT SEARCH CONFIGURATION da ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, danish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION fi ( COPY = finnish );
    ALTER TEXT SEARCH CONFIGURATION fi ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, finnish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION hu ( COPY = hungarian );
    ALTER TEXT SEARCH CONFIGURATION hu ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, hungarian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION it ( COPY = italian );
    ALTER TEXT SEARCH CONFIGURATION it ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, italian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION no ( COPY = norwegian );
    ALTER TEXT SEARCH CONFIGURATION no ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, norwegian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION pt ( COPY = portuguese );
    ALTER TEXT SEARCH CONFIGURATION pt ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, portuguese_stem;
    
    CREATE TEXT SEARCH CONFIGURATION ro ( COPY = romanian );
    ALTER TEXT SEARCH CONFIGURATION ro ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, romanian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION ru ( COPY = russian );
    ALTER TEXT SEARCH CONFIGURATION ru ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, russian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION es ( COPY = spanish );
    ALTER TEXT SEARCH CONFIGURATION es ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, spanish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION sv ( COPY = swedish );
    ALTER TEXT SEARCH CONFIGURATION sv ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, swedish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION tr ( COPY = turkish );
    ALTER TEXT SEARCH CONFIGURATION tr ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, turkish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION usimple ( COPY = simple );
    ALTER TEXT SEARCH CONFIGURATION usimple ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, simple;
    
    

    public by yourfriendcaspian modified Aug 29, 2017  46  0  1  0

    Create mysql user with specific permissions

    Create mysql user with specific permissions: mysql_grant.sql
    CREATE USER 'joe'@'%' IDENTIFIED BY 'xxxxxx';
    # ALL PRIVILEGES is everything except grant
    GRANT ALL PRIVILEGES ON APP_production_temp.* TO 'joe'@'%'; 
    GRANT SELECT ON APP_production.* TO 'joe'@'%';
    GRANT SELECT ON stats_production.* TO 'joe'@'%';
    
    

    public by cwilli34 modified Feb 21, 2017  101  0  2  0

    Set SQL database to single user mode

    Set SQL database to single user mode: database_single_user.sql
    USE master;
    GO
    ALTER DATABASE AdventureWorks2012
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
    GO
    ALTER DATABASE AdventureWorks2012
    SET READ_ONLY;
    GO
    ALTER DATABASE AdventureWorks2012
    SET MULTI_USER;
    GO
    
    

    public by bobby modified Jan 10, 2017  157033  2  4  0

    Returns Descriptions for all active Magento products

    Returns Descriptions for all active Magento products: Magento-active-product-descriptions.sql
    SELECT 
        e.entity_id,
        e.sku,
        eav.value AS 'description',
        IF(at_status.value_id > 0,
            at_status.value,
            at_status_default.value) AS `status`
    FROM
        catalog_product_entity e
            JOIN
        catalog_product_entity_text eav ON e.entity_id = eav.entity_id
            JOIN
        eav_attribute ea ON eav.attribute_id = ea.attribute_id
            INNER JOIN
        `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`)
            AND (`at_status_default`.`attribute_id` = '89')
            AND `at_status_default`.`store_id` = 0
            LEFT JOIN
        `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`)
            AND (`at_status`.`attribute_id` = '89')
            AND (`at_status`.`store_id` = 1)
    WHERE
        ea.attribute_code = 'description'
            AND IF(at_status.value_id > 0,
            at_status.value,
            at_status_default.value) = 1
    LIMIT 9999
    
                

    public by JulioTrujillo modified Nov 15, 2016  7563  0  4  0

    Anonimous block

    SQL
    cl scr
    set lin 200
    SET SERVEROUTPUT ON
    SET TERMOUT ON
    
    DECLARE 
    
    BEGIN
    
    END;
    /

    public by sjschmalfeld modified Oct 31, 2016  298468  1  4  1

    Kill Connections to a Database SQL Server

    sql
    // KILL CONNECTIONS TO A DATABASE
    
    USE [master]
    GO  
    
    DECLARE @kill varchar(8000) = '';  
    SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
    FROM sys.dm_exec_sessions
    WHERE database_id  = db_id('MyDB')
    
    EXEC(@kill);
    • Public Snippets
    • Channels Snippets