''First'' not supported in SQL Server

Question

Post reply

''First'' not supported in SQL Server

Mak Mohan

SSC Eights!

Points: 994
More actions
September 13, 2007 at 11:36 pm

#178347

I am using following query in access
Select first(fname),age from mytab group by age
Database   :

Sam     21
Raj      21
Karan   21
John    23
Rocky 23
O/P of Query :
Sam   21
John   23
I want to use the same query in SQL Server 2005,But First keyword is not supported in SQL Server 2005.So how can I write my own function 'first' in SQL Server 2005, so that I will not need to change above query in SQL Server also and it should give same result.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Have you tried using

TOP

or

PERENT

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

SELECT Name, Age FROM (

SELECT Name, Age, ROW_NUMBER() OVER (PARTITION BY Age ORDER BY Name) AS RecID FROM MyTab

) AS d WHERE RecID = 1

SELECT Age, MIN(Name) FROM MyTab GROUP BY Age

N 56°04'39.16"
E 12°55'05.25"

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 3

In your Access query, FIRST just retrieves a value of the name column from some random row. Assuming you don't actually require a random result but don't mind which name is returned just substitute MIN or MAX in place of FIRST in your query.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

SELECT MIN(fName),Age

FROM yourtable

GROUP BY Age

Although not sure why you want to give the alphabetically earlier names such a break. Why do you need to do this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

MSAccess was where I ran into First() as an aggregate function. It was at first glance a bit useless, but it was actually used to help make your queries run a little faster by keeping the GROUP BY field list small. The idea was - if you could keep the GROUP BY to fields that were indexed - it would do its job better.

An example might be better: Suppose you have an employee table, and an absences table (with dates and reasons). Then a report to assess how many times you'd been absent by reason code might be:

Select e.employeeID, first(e.Firstname),first(e.Lastname), a.reason, count(a.date)

from employeelist e inner join absences a on e.employeeid=a.employeeid

group by e.employeeID, a.reason

The firstname/lastname fields don't "add" any value to the GROUP BY, so they just "complicate" matters by being there.

----------------------------------------------------------------------------------
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?