Get 1st Value in Group

I have a grouping view and I want to be able to get the 1st value in a
column. In Access I would use First(colname) but I couldn't fing anything
similar in BOL. Thanks.
DavidIn SQL Server there is no direct equivalent of the Access FIRST and
LAST
functions. A table in SQL is not logically ordered so there is no first
or
last row - you have to specify the column/expression that determines
which
rows you want.
Here's an example taken from the Pubs database giving the first and
last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on
some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in
which
they were entered into a table then you need to add a DATETIME column
to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||FIRST has no meaning in a relational database. Maybe you are looking for
MIN or MAX. Or, if you show your table structure, sample data and desired
results (see http://www.aspfaq.com/5006), someone can give you more explicit
help.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OYJV5aSGFHA.428@.TK2MSFTNGP15.phx.gbl...
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing anything
> similar in BOL. Thanks.
> David
>|||David C wrote:
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing
> anything similar in BOL. Thanks.
> David
What do you mean "First value in a column"?
A column only contains a single value. Are you dealing with some sort of
delimited set of values stored in a single column? If so, you can use
CHARINDEX() function to look for the delimiter you use to delimit the
"words" and then use SUBSTRING() to pull it out.
Probably better to do this on the client, though.
Can you post your DDL and example data.
David Gugick
Imceda Software
www.imceda.com|||This is what I am using.
SELECT TOP 100 PERCENT PersonID, MIN(DISTINCT HistoryText) AS
FirstOfHistoryText
FROM dbo.HistoryDates
WHERE (DateCode = N'H')
GROUP BY PersonID
ORDER BY MAX(HistoryDate) DESC
I thought that it would give me the HistoryText value for the PersonID
for the latest HistoryDate with DateCode = 'H' for that PersonID.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Try:
SELECT personid, historytext
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
However, if you have duplicate values in the HistoryDate column that
might return more than one row for a given PersonId. Maybe that's not
what you want, in which case you might do:
SELECT personid, MIN(historytext)
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
GROUP BY personid
Hopefully you can see now that this won't return the "first" or "last"
of anything - tables have no fixed concept of order. These queries
return the row(s) for the maximum date. In the case of the second
query, you'll get, at most, one row per PersonId with the
MIN(historytext) value.
David Portas
SQL Server MVP
--|||Yes, there will be multiples for any PersonID. I want to get the text
and date from the MAX(HistoryDate) for each PersonID. I will try your
last suggestion.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David,
I think you have the N'H' conditions in the wrong place.
Your query might return nothing for some personid, if
for the latest historydate it is not the case that datecode = N'H',
even if that person does have some rows where datecode = N'H'
I think (but am not sure) that David wants exactly one
result row for every personid with any N'H' rows, and the
one he wants is the latest of those N'H' rows.
If (personid, historydate) is unique, you can just move the
(datecode = N'H') condition into the subquery. If only
(personid, historydate, datecode) is unique, then it should
be in both the main query and the subquery.
Steve Kass
Drew University
David Portas wrote:

>Try:
>SELECT personid, historytext
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
>However, if you have duplicate values in the HistoryDate column that
>might return more than one row for a given PersonId. Maybe that's not
>what you want, in which case you might do:
>SELECT personid, MIN(historytext)
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
> GROUP BY personid
>Hopefully you can see now that this won't return the "first" or "last"
>of anything - tables have no fixed concept of order. These queries
>return the row(s) for the maximum date. In the case of the second
>query, you'll get, at most, one row per PersonId with the
>MIN(historytext) value.
>
>