SQL - Get the first record only

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

select top 1 ......

hallo,
Cristiano

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

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

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

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

Couldn't you try this.

SELECT DISTINCT KeyWordCode FROM ProdKeyword WHERE ProdID = 5


David Paulson

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.