How to return the first line from each set of data

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

General SQL Server Forums

New to SQL Server Programming

How to return the first line from each set of data

Author

Topic

kelemvor
Starting Member

6 Posts

Posted - 2011-07-28 : 16:10:39

So I have a bunch of records to record sessions a user is in the system. Each session has a unique ID but has multiple lines that make up the entire session.

I simply need to get a list of sessions and the date/time it started.

I originally just did a SELECT DISTINCT and only pulled the date (not time) and the session ID which worked fine. However I now need the time as well which means every record is different.

There is an ID column that every entry has a unique ID so maybe if I said to give me the entry with the lowest ID of each set of Sessions... but I'm not sure how to make that work.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 17:19:14

Would picking the row with the min time get you what you need?

select
   session_id,
   min(datestamp) as start_time
from
   yourTable
group by
   session_id

You could try to get the minimum unique Id for each session, but then you will have to join that result with the table to get the start time.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-28 : 20:36:46

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

>> So I have a bunch of records [sic: rows are not records] to record sessions a user is in the system. Each session has a unique ID but has multiple lines that make up the entire session. <<

How nice for you! What does it look like?

>> There is an ID column that every entry has a unique ID so maybe if I said to give me the entry with the lowest ID of each set of Sessions... but I'm not sure how to make that work. <<

That vague narrative screams an invalid design. It sound like there ought to be a compound key of (session_id, event_id).

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources