SQL - Get the first record only

[Raptors Fan]

Hi,

I need to get the first record found in a table. What I mean is the following: table ProdKeyword has ProdID and KeyWordCode fields. A product may have more than one keyword, so for example ProdID 5 may be given the keyword "x" and "y".
So, on the table ProdKeyword, there would be two records for ProdID 5:
ProdID KeywordCode
-------- ---------
5 x
5 y

When I query the table ProdKeyword for ProdID such as:

SELECT KeyWordCode
FROM ProdKeyword
WHERE ProdID = 5

... the two records return, what I want is that it returns ONLY the first record it finds where ProdID = 5

How can you do this in SQL?

Many Thanks,

RF

[clarghi]

select top 1 ......

hallo,
Cristiano

[Raptors Fan]

no such keyword "TOP", I'm using Transact-SQL, what are you using?

[michi]

Hi
'TOP' is a Keyword in T-SQL. You can find it on SQL Server Books Online.

===
SELECT top 1 KeyWordCode
FROM ProdKeyword
WHERE ProdID = 5
===

Regards,

Michi
MCSE, MCDBA

[Raptors Fan]

My bad ;-) ..... the word TOP does exist... but I'm still getting errors on SQL 7 but not in ACCESS 97.

I tried the following SQL statement just to try it out on a table I made quickly with a few records:

SELECT TOP 1 *
FROM Customer

.. And yes, it returns only one record, but when I try it on the real table sitting on SQL 7 I get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

Whyyyyyyy? :(

I looked up help and saw the syntax for SELECT where it does show TOP but no example with TOP being used.

Any help is appreciated.

Many thanks,

RF

[michi]

I've tested the syntax on some of my REAL tables of SQL 7 (not in Access 97), no problem. What I did is:
========
Select top 1 myfield1, myfield2 from mytable
=========

If you can query on the Customer table, why not your real table? What the exact codes which caused the problem?



Regards,

Michi
MCSE, MCDBA

[d.paulson]

Couldn't you try this.

SELECT DISTINCT KeyWordCode FROM ProdKeyword WHERE ProdID = 5


David Paulson

[Raptors Fan]

No, the way DISTINCT works is that it removes duplicates...so, it still will return more than one record, if the records are different from each other. TOP 1 will only return the first record found. Well, that is my understanding of it. I also realized that the database is not SQL 7 but SQL 6.5, and I'm not sure if that makes a difference.... I'm thinking maybe the T-SQL in SQL 6.5 is missing some keywords which are in SQL 7.0.