May 28, 2008 at 3:05 am
Hi there,
i am implementing something in SQL where i need one function which can perform same thing as First function in Access.
If we have different record for some particular reference number then i need to select first record. Access have First function but SQL doesnt have it.
i have query something like this.
select referece, date, first(ward), first(something)
from table X
group by reference, date
i can use TOP 1 in SQL but group by makes confusion.
any suggestion?
thanks,
vijay
May 28, 2008 at 4:09 am
Use the ROWCOUNT Setting as below:
SET ROWCOUNT 1
select referece, date, first(ward), first(something)
from table X
group by reference, date
Regards,
Samata
May 28, 2008 at 4:12 am
Hi,
there is no first function in SQL Server. So First(ward) cant work.
thanks,
vijay
May 28, 2008 at 4:48 am
oops sorry, I misread your query.
Try the below query and check if it serves ur purporse:
select t.referece, t.date, (select top 1 ward
from tableX as t1 where t.reference=t1.reference) as ward,
(select top 1 something
from tableX as t1 where t.reference=t1.reference) as something
from tableX as t
group by t.reference, t.date
May 28, 2008 at 5:06 am
You can use either TOP 1 or MIN / MAX functions, but in both cases you will need to filter out duplicate rows using GROUP BY (or, in case of subselect with TOP 1, you can use SELECT DISTINCT instead of grouping with the same result).
SQL Server does not have any information about what row is "first" or "last", such thing as "first row" does not exist. That means, "SELECT TOP 1 * FROM table" can give different results on each execution. Most of the time you will get the same row, but it is not guaranteed and it can depend on things outside the actual data - like indexes. To be sure you select always the same row, ORDER BY clause is necessary.
If you simply want any value returned, you can use
SELECT reference, date, MIN(ward), MIN(something)
FROM table X
GROUP BY reference, date
... but keep in mind that in this case both values are checked independently - value of "ward" can easily come from a different row than value of "something".
May 28, 2008 at 5:49 am
You should look to use the TOP (1) with an ORDER BY statement. You can modify the ORDER by to get a descending value, going from highest to lowest, by adding DESC to the order by. For a single statement, something like this:
SELECT TOP ( 1 )
v.*
FROM dbo.Version v
WHERE v.DocumentId = 433
ORDER BY v.DocumentId DESC,
v.VersionId DESC
To get more complicated, say to select only the top values within sets of records, do something like this:
SELECT d.[DocumentName]
,d.[DocumentId]
,v.[VersionDescription]
,v.[VersionId]
FROM dbo.[Document] d
JOIN dbo.[Version] v
ON d.[DocumentId] = v.[DocumentId]
AND v.[VersionId] = (SELECT TOP (1) v2.VersionId
FROM dbo.[Version] v2
WHERE v2.DocumentId = v.DocumentId
ORDER BY v2.DocumentId, v2.VersionId DESC
)
WHERE d.[DocumentId] = 9729
Stay away from doing sub-selects within the SELECT criteria of your query because that just turns the query into a cursor, also known as row-by-agonizing-row (RBAR) processing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 29, 2008 at 2:21 am
hi,
it works fine. thank you for your help.
regards,
vijay
June 12, 2008 at 8:03 pm
The First() function in MSAccess returns the first record entered into the table.
SELECT TOP 1 is not the equivalent.
Try creating a new table in MSAccess with one string field. Enter in records B then A then C. If you then do a First() on this table you will get B. SELECT TOP 1 will return A if you order ascending and SELECT TOP 1 will return C is you order DESC therefore SELECT TOP 1 is not an equivalent no matter what ORDER BY you use.
June 13, 2008 at 6:06 am
While that may be true, SQL Server is not Access. There's no way to ensure physical order of the data returned without applying some type of order to the statement. Without further information such as an identity field, a datetime, an ordered guid or a timestamp, there's no way to ensure physical ordering on data returned. You have to ORDER BY something.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 13, 2008 at 6:40 am
hmm... didn't notice the double-post until now....
Please don't cross-post - you end up diluting/confusing the conversation. Besides you tend to piss off regular posters so you might get less help as a result.
the discussion has raged on over here:
http://qa.sqlservercentral.com/Forums/Topic354263-8-2.aspx#bm516388
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 13, 2008 at 7:27 am
Whoa! What a mess. So, in short, FIRST() functions in a fashion somewhat, but not entirely, similar to TOP(1)?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 13, 2008 at 7:36 am
Grant Fritchey (6/13/2008)
Whoa! What a mess. So, in short, FIRST() functions in a fashion somewhat, but not entirely, similar to TOP(1)?
Essentially. The main difference in my mind is that because it is an aggregate function, you can use it to return grouped "firsts" without some type of CSQ against a DISTINCT. Otherwise, the same behaviors can be duplicated with Top (1) (with or without an ORDER BY depending on the effect you're after).
2005 with the Ranking functions and CTE's give you a lot of other options to go after the same kind of effect.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply