Sql query

Copper Contributor

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

12 Replies

Hi @Alex_Kim245 --

 

Try something like the query below.  Take care.

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

bake13_0-1643200458397.png

 

 

This will not work because TX has Cisco and Dell also. I'm trying to get the ones that only have HP.

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'	
Getting syntax error in From clause.

Hi @Alex_Kim245 --

 

What is the error, please?

syntax error in From clause.
this section right here;
FROM
Vendor
) t
table name is table1 so Vendor should be table1? and what is t?

Hi @Alex_Kim245 --

 

Would you be able to paste the error message or screenshot of the error that you are receiving?  I tested on SQL 2019 and am not encountering any errors.  Take care.

I'm running it on Access and this is the query;
Select [table1].location
From (
Select [table1].vendor,
[table1].location,
rownum( ) over (partition by [table1].location order by [table1].vendor) as rownum
From [table1].vendor) [table1]
Where rownum = 1 and [table1].vendor = "HP"

Hi @Alex_Kim245 --

 

That query will only work on SQL Server and is incompatible with Access.  Unfortunately I do not know the Access-equivalent syntax and do not have Access available on which to test.  Take care.

I see. Thanks for your help!

@Alex_Kim245 try asking your question in the Access Community