Returning first entries in reverse order

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

Returning first entries in reverse order

Author

Topic

DaitarnGe
Starting Member

8 Posts

Posted - 2007-02-26 : 19:36:25

Hi,
I need to get first entries orderd by datetime asc, but I need the newest of this block returned first.
Imagine to have some entries:

08:00 ...
09:00 ...
10:00 ...
.... ...
15:00
16:00
17:00
....
I want to get the first 3 but the highest in time first.
Like this:
10:00 ...
09:00 ...
08.00 ...

Any idea?
Thanks a lot
Francesco

DaitarnGe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 19:42:14

SELECT TOP 3 YourTimeColumn
FROM YourTable
ORDER BY YourTimeColumn DESC

Tara Kizer

DaitarnGe
Starting Member

8 Posts

Posted - 2007-02-26 : 20:10:58

quote:
Originally posted by tkizer

SELECT TOP 3 YourTimeColumn
FROM YourTable
ORDER BY YourTimeColumn DESC

Tara Kizer

Sure? I think
ORDER BY YourTimeColumn DESC will return first:
17:00
16:00
15:00

Francesco?

DaitarnGe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 20:15:05

How do you determine what is newest in the table?

Tara Kizer

DaitarnGe
Starting Member

8 Posts

Posted - 2007-02-26 : 20:21:17

Sorting it by datetime ASC, the last entry in the block is surely the newest. Isn't it?

DaitarnGe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 20:23:43

It depends if GETDATE() or the current date/time is being sent to your datetime column. If it isn't, then is GETDATE() at least the default.

You can never make an assumption as to the order in which the data was inserted.

Tara Kizer

DaitarnGe
Starting Member

8 Posts

Posted - 2007-02-26 : 20:30:55

I need to get a block of data most old in a period of time but I have to know first the newest of this block.
So I put statement WHERE datetime >= 08:00 AND datetime <= 10:00, but i need to return first the newest, that is 10:00, then 09:00 and 08:00

DaitarnGe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 20:36:10

I am not understanding what you want. Please provide a data example that shows columns and data in each column. Also show us the exact result set you want returned based upon the data provided.

Tara Kizer

DaitarnGe
Starting Member

8 Posts

Posted - 2007-02-26 : 20:45:12

ok.
Imagine a large block of entries in a database where there is a colum TimeStamp that contain DateTime and other colums of data.
I have to select 100 oldest rows. So I order them by TimeStamp ASC and get the top 100.
Now I need to know what is the datetime of the last entry.
If I could get it for first my job was done.
But I have to read one by one until I get the last, and this is time consuming for me.
I don't know if I explained well, sorry for my poor english.

DaitarnGe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 20:47:53

Please post 10 example rows of data. Then post what you want to see in the result set based upon these 10 rows.

A data example will illustrate exactly what you want regardless of how good or bad your English is.

Tara Kizer

DaitarnGe
Starting Member

8 Posts

Posted - 2007-02-26 : 20:53:19

ID: TimeStamp Data
1 02:00 xyz
2 05:00 xyz
3 06:00 xyz
4 03:00 xyz
5 08:00 xyz
6 04:00 xyz
7 12:00 xyz
8 13:00 xyz
9 10:00 xyz
10 11:00 xyz

Result:
6 04:00 xyz
4 03:00 xyz
1 02:00 xyz

Francesco

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 21:02:13

I don't get how you know to grab 04:00, 03:00, 02:00 based upon the data that you have shown.

13:00, 12:00, 11:00 are certainly newer in time aspects than your other data.

Tara Kizer

DaitarnGe
Starting Member

8 Posts

Posted - 2007-02-26 : 21:12:23

I can submit COUNT of 3 items, insn't it?
I know this is and hard issue, but I need only a block of data.
If I do SELECT COUNT TOP 3 ORDER BY TimeStamp ASC WHERE TimeStamp >= 00:00 AND TimeStamp <= 23:59 I can get: 02:00, 03:00, 04:00 ... but I need the highest first (04:00)

???
Anyway
Thanks a lot for helping
I go to bed.
Tomorrow an hard day at work :)

Francesco

Fatbug
Starting Member

5 Posts

Posted - 2007-02-26 : 22:49:31

Hey,

not sure if this is what you want. Seems too simple compared to all the discussion going on here. Anyway i'll post it, plz keep all sarcastic comments to a min if i'm off track

.

select a.idnum, a.times
from (select top 3 times, idnum
from zztimetable
order by times asc) as a
order by a.times desc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-27 : 00:58:18

quote:
Originally posted by Fatbug

Hey,

not sure if this is what you want. Seems too simple compared to all the discussion going on here. Anyway i'll post it, plz keep all sarcastic comments to a min if i'm off track .

select a.idnum, a.times
from (select top 3 times, idnum
from zztimetable
order by times asc) as a
order by a.times desc

I think you nailed it! I was just having such a hard time trying to figure out what he wanted as it didn't make sense to me. Now that I've seen your query, I now understand his question. It's weird how when it takes a certain query to be able to understand what the poster was asking in the first place.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 01:09:34

select timestamp from (select top 3 timestamp order by timestamp) order by timestamp desc

Peter Larsson
Helsingborg, Sweden

DaitarnGe
Starting Member

8 Posts

Posted - 2007-02-27 : 06:15:13

Thanks to Fatbug, it worked as expected.

Tara, thank you for support

Francesco

joblenis
Starting Member

29 Posts

Posted - 2007-04-18 : 14:35:35

Is there a reverse ORDER BY command? I want to list the more occurences first, right now it is opposite. Thanks

SELECT [cIp], count(1)
FROM [IISLOG_REPORTS].dbo.tblIISLog_ALL
GROUP BY [cIp]
ORDER BY count(1)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 14:50:59

ASC is the default. So if you don't specify which, then ASC is used.

Change it to:

ORDER BY ... DESC

Tara Kizer
http://weblogs.sqlteam.com/tarad/

joblenis
Starting Member

29 Posts

Posted - 2007-04-18 : 15:27:42

TY kindly. I figured there was an easy way, and I didnt understand the ASC used earlier in the post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources