Forum Discussion

Alex_Kim245's avatar
Alex_Kim245
Copper Contributor
Jan 25, 2022

Sql query

Could someone help me construct query to find location that only has HP in below table;

vendor location

HP       TX

Cisco   TX

Dell     TX

HP      CA

HP      WA

Dell    WA

HP     OR

Cisco  OR

HP     WI

Dell   WI

Cisco WI

  • Hi Alex_Kim245 --

     

    Try something like the query below.  Take care.

    SELECT
    	Vendor
    	,VendorLocation
    FROM
    	Vendor
    WHERE
    	Vendor = 'HP'

     

     

    • Alex_Kim245's avatar
      Alex_Kim245
      Copper Contributor
      This will not work because TX has Cisco and Dell also. I'm trying to get the ones that only have HP.
      • bake13's avatar
        bake13
        Icon for Microsoft rankMicrosoft

        Hi Alex_Kim245 --

         

        Apologies, I misunderstood.  Something like the query below might work.  Take care.

        SELECT *
        FROM (
           SELECT 
        		Vendor
        		,VendorLocation
                ,row_number() over (partition by VendorLocation order by Vendor) as rownum
          FROM
        		Vendor
        ) t
        WHERE
        	rownum = 1
        	AND
        	Vendor = 'HP'	

Resources