FIRST() function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

FIRST() function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-06 : 12:25:55

Swaminathan writes "Hello,

Do we have any function to get first record
for each group

ex:

we have table: pagesess (pageid varchar,sessid varchar, cmdid varchar, cmdtime bigint)

for each sessid I need the first(not min) pageid.
meaning what is the first page the user(sessid) visited.

I have a function called first() MSAccess. And the query will
be select first(pageid) from pagesess group by sessid.

Pl tell me how to do it in SQL server

Thanks in advance

Swami"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-06 : 12:41:24

With this particular table design, what you are looking for is impossible because nothing defines the first page visited. At first I thought the cmdtime column was going to be a datetime, indicated the timestamp of the page request, but I am assuming by the datatype that that isn't it . . .

If you had a pagerequestedon datetime column, you could do something like . . .


select
    pageid
from
    pagesess p
where
    not exists (
        select 1
        from
            pagesess
        where
            p.sessid = sessid and
            p.pagerequestedon > pagerequestedon )

<O>

swami_v
Starting Member

1 Post

Posted - 2002-06-07 : 02:58:18

Thanks for your reply.
But what I need is not based on cmdtime or time.
I just need the first record for each session.
That will get me the first pasge visited by the user.
The cmdtime may be the same. Is there any other way
we can get it. I need the equivalent of FIRST()
function in MS Access.

Thanks
Swami

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-06-07 : 05:01:20

There is no direct function.
May be this can help you...

----
create function ShowFirst()
returns @y table(pageid varchar(10), sessid varchar(10))
as
begin
declare @sessid varchar(10)
declare @temp table(sessid varchar(10))

insert into @temp
select distinct sessid from pagesess

select top 1 @sessid = sessid from @temp
while @@rowcount <> 0
begin
insert into @y
select top 1 pageid, sessid from pagesess where sessid = @sessid

delete from @temp where sessid = @sessid
select top 1 @sessid = sessid from @temp
end
return
end
-----------

Ramesh

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 07:08:32

select o.sessid, o.pageid
from pagesess o
where o.pageid = (select top 1 o2.pageid from pagesess o2
where o2.sessid = o.sessid)

There are a couple of factors that might bugger this up but the principle is right.
Here is another example that you can test against the Northwind database:

select o.customerid, o.orderid
from orders o
where o.orderid = (select top 1 o2.orderid from orders o2
where o2.customerid = o.customerid)

I recommend that you create a clustered index or a unique identifier that will enable you to use min(). A cached (other) query might change the order of the records if the order is not explicitly defined.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-07 : 09:56:29

NO NO NO!!!!

You have no measure of FIRST. There is nothing to guarrentee that TOP 1 will bring back the first page of the session. If you don't have some time measure, there is no friggen way to tell which page was the first for the session.

I'm real sorry boys, but rksingh024 and dataphile are leading down the wrong path.

If you create a table and insert 5 records into it, there is no relation priciple, there is no SQL Server directive, that mandates that select top 1 will bring back the FIRST record (of the 5) you inserted. This is true regardless of indexes. (NOTE: yeah, its true that most times it will, but that doesn't change the facts) In order to guarrentee select top 1 will give you the first record, you MUST HAVE an ORDER BY clause. In your DDL, you have provided nothing to ORDER BY, and therefore your request is NOT POSSIBLE!!

Somebody back me up here.

<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-07 : 10:08:28

Folks,
Page is correct. FIRST() is not a relational idea, it's not a t-sql function and it is not part of the ANSI SQL standard. It exists only as an artifact of Microsoft Access. It's a shame it is implemented at all because it only serves to confuse developers, like yourself, who use it and then move to a true relational product like SQL Server and wonder why it's not there.

In the relational model, you can ONLY select a row based on its column values. NOTHING ELSE. There is no concept of row ordering. There is no first, no last, no thirty-fifth. So, if you want the first page the user visited in the session, you would have to find the row with that particular session ID and having the earliest timestamp, more than likely.

To say for sure we will need to see your DDL ( your table definitions ) so we can see what your table layouts are.

setBasedIsTheTruepath
<O>

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 10:09:07

Isn't that what I said?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-07 : 10:13:14

Thanks, Set . . .

quote:

...a true relational product like SQL Server...
<O>

carefull now, you don't want to upset the relational gods with this kinda blasphamy ...

quote:

...a more true to relational concepts, but not quite 100% true relational, product like SQL Server...
<O>

<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-07 : 10:16:21

quote:

Isn't that what I said?

My apologies ... I did repeat your answer of using a min() aggregate but added the requisite non-relational-thinking-bashing.

setBasedIsTheTruepath
<O>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-07 : 10:21:08

quote:

Isn't that what I said?

Sort of, yeah, but not really. The dml provided as a solution won't work. I read you disclaimer, but it not quite right. Even with a clustered index, a min doesn't provide the first in time . . .



create table page47 (
    dataphile int not null primary key,
    someothercolumn char(1) not null )
go
insert page47 select 2,'a'
insert page47 select 1,'b'
go
select
    someothercolumn
from
    page47 a
where
    dataphile = (select min(dataphile) from page47)
go

BOTTOM LINE...IF YOU WANT TO SORT BY TIME YOU NEED TO HAVE A TIME COLUMN TO SORT ON!!!

<O>

Edited by - Page47 on 06/07/2002 10:24:16

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 10:23:17

Set...
No, sorry.
Your reply was posted while I was typing. You win again :)
No hard feelings.

Page47
You're right. It is so fundamental I assumed the pageid to be incremental. No excuse though.

Edited by - dataphile on 06/07/2002 10:29:37

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-07 : 10:26:30

quote:

No, sorry.
Your reply was posted while I was typing. You win again :)
No hard feelings.

I'm confused, are you apologizing for setbased for something or retracting your statements about the clustered index providing the sorting by time? Who is winning?

EDIT: I'm sorry, but this is a very very important and very frequently misunderstood subject matter. I want to make sure the point I am trying to get across is clear . . .

<O>

Edited by - Page47 on 06/07/2002 10:29:45

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 11:10:26

create table test(field1 varchar)
go
create clustered index cx_field1 on test(field1)

For a moment I had thought that the oldest record would always show up in
"select top 1 field1 from test" --NOT

Point taken.

Lady
Starting Member

32 Posts

Posted - 2002-06-11 : 04:08:26

sorry, for interrupting you? but if I have identity column - you could fing first record easy.
Is'n it?

Nazim
A custom title

1408 Posts

Posted - 2002-06-11 : 04:26:32

Lady , you havent got the point yet. read Page47 and setbased posts. what they are trying to emphasise is that therez nothing like storing in a ordered way in SQL SERVER or for that matter any relational dbms.

now what you are suggesting is somethign based on min or max function. thts fine. but DONT confuse and mislead yourself by thinking that a identity column or for that matter anyother column will store the data in a sorted order. its only when you use specific functions like min, max etc you can get the first or the last value you entered(ofcourse if you enter in that order). as page has already clarified when you retreive the data the sorting can never be guarteed until a order by clause is used.

quote:

In the relational model, you can ONLY select a row based on its column values. NOTHING ELSE. There is no concept of row ordering. There is no first, no last, no thirty-fifth.

and not even 47

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - Nazim on 06/11/2002 09:51:56

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-11 : 09:43:04

quote:

sorry, for interrupting you? but if I have identity column - you could fing first record easy.
Is'n it?

To add to Nazim's post . . .

Swami doesn't have the identity property defined for any of his columns. Hence the importance providing full DDL when asking questions on this board.

Second, while identity column implies the insert order,a) IDENTITY is a product specify construct and thus not a relational requirement and b)is not a guarentee of uniqueness and c)can easily be mucked with by dbcc functions. I believe that if you want to query based on time, you're best design should store time. Identity won't tell you which pages where requested within the first 10 minutes of logon, etc.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources