Sql query

[Alex_Kim245]

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

[#2]

Hi @Alex_Kim245 --

Try something like the query below.  Take care.

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

[#3]

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

[#4]

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'	

[#5]

Getting syntax error in From clause.

[#6]

Hi @Alex_Kim245 --

What is the error, please?

[#7]

syntax error in From clause.

[#8]

this section right here;
FROM
Vendor
) t
table name is table1 so Vendor should be table1? and what is t?

[#9]

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.

[#10]

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"

[#11]

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.

[#12]

I see. Thanks for your help!

[#13]

@Alex_Kim245 try asking your question in the Access Community