possible to return table reversed? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

possible to return table reversed?

Author

Topic

capn_wayne
Starting Member

9 Posts

Posted - 2004-12-01 : 12:47:02

Sorry for the newbie question but I've been searching the forums and haven't had much luck so far, so I thought I'd just ask. Understood that a table has no 'real' top and bottom but it 'appears' that new records are appended to the 'bottom' of the table. With that, I have a basic table with a date field but no time/stamp field. Is it possible just to return the records in the reverse order they were 'appended' to the table? Is there some SQL Svr '&' function or something behind the scenes of the table itself to allow this? (I saw the 'ORDER BY 1' - does that just sort by column '1', or the first column, etc.?)
Thanks in advance for any help offered.

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 12:57:42

You have the DDL of the table?

IS there an IDENTITY Column?

And when you say it has a "DATE" field, Only SQL Server 2005 will have that data type, so is it varchar?

If it is datetime, do you have it set to default to GetDate() (and that could contain dups as well).

ORDER BY 1 means order by the 1st column in the select list.

And data is added to pages...it'll try and find space on the first available page, so new data can be scattered all over the place. If all the pages are full it'll start a new page.

Think of it like a notebook where each page has writting on it, but only (and this depends on how you set it up) 80%.

You want to add a new item, you write it at the bottom of the first page, if it's free, regardless of it's key information.

Just curious, what is it that you want to do?

Brett

8-)

capn_wayne
Starting Member

9 Posts

Posted - 2004-12-01 : 13:28:12

I'm just using SS Ent.Mgr so the SELECT statement just makes it appear that they're appended. Would the DDL just be the SQL I'm typing or the Collation Name of LATIN1_GENERAL_BIN for the table? (Not sure how to answer the DDL question.)

No IDENTITY column. Just using a uniqueidentifier that's auto-created.

The Date field is set as smalldatetime DataType.

I'd like to display the table from the 'last record entered' first, if that makes sense. In other words, the next record that's appended appears at the top of the grid I'm using to display the data they're entering.

I guess I should probably just add the time/stamp and sort DESC using that.

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 13:35:12

Do you know what query analyzer is?

Also for the DDL, in Enterprise Manager(EM) right click on the table, then click on all tasks, the say generate sql script..click on the 3rd options tab a check primary keys, ect

Then go back to the first tab, and click preview.

Highlight that code and paste it here

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-01 : 13:35:30

quote:
Originally posted by capn_wayne
I guess I should probably just add the time/stamp and sort DESC using that.

You just might be on to something!

You might be surprised to find that very often, the simpliest, most intuitive and most direct solution is also the best solution.

- Jeff

capn_wayne
Starting Member

9 Posts

Posted - 2004-12-01 : 13:41:30

I have used Query Analyzer but, old habits led me back to the E.M.


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GRINDER_DATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GRINDER_DATA]
GO

CREATE TABLE [dbo].[GRINDER_DATA] (
	[Device] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[Date] [smalldatetime] NULL ,
	[MOT] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[Shift] [varchar] (3) COLLATE Latin1_General_BIN NULL ,
	[Grade_Alloy] [varchar] (10) COLLATE Latin1_General_BIN NULL ,
	[Start_Size] [float] NULL ,
	[Finish_Size] [float] NULL ,
	[Bundles] [varchar] (20) COLLATE Latin1_General_BIN NULL ,
	[Piece_Cnt] [varchar] (10) COLLATE Latin1_General_BIN NULL ,
	[RPM] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[FPM] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[Tools] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[Bushings] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[Oper_Init] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[Comments] [varchar] (150) COLLATE Latin1_General_BIN NULL ,
	[Tol] [varchar] (20) COLLATE Latin1_General_BIN NULL ,
	[Passes] [varchar] (20) COLLATE Latin1_General_BIN NULL ,
	[Start_Weight] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[End_Weight] [varchar] (15) COLLATE Latin1_General_BIN NULL ,
	[p_key] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GRINDER_DATA] ADD 
	CONSTRAINT [DF_GRINDER_DATA_p_key] DEFAULT (newid()) FOR [p_key]
GO

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 13:55:55

Too bad you don't have an identity. Too bad you don't use QA.

Cut and paste this code in to a QA window. It should just run.

It shows the Ordering by the date DESC can be problematic


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 datetime default getdate(), Col3 varchar(10))
GO

INSERT INTO myTable99(Col3)
SELECT 'A'
INSERT INTO myTable99(Col3)
SELECT 'B'
INSERT INTO myTable99(Col3)
SELECT 'C'
INSERT INTO myTable99(Col3)
SELECT 'D'
INSERT INTO myTable99(Col3)
SELECT 'E'
GO

SELECT * FROM myTable99 ORDER BY Col2 DESC

SELECT * FROM myTable99 ORDER BY Col1 DESC
GO

SET NOCOUNT OFF
DROP TABLE myTable99

The Results


Col1        Col2                                                   Col3       
----------- ------------------------------------------------------ ---------- 
5           2004-12-01 13:54:45.517                                E
2           2004-12-01 13:54:45.500                                B
3           2004-12-01 13:54:45.500                                C
4           2004-12-01 13:54:45.500                                D
1           2004-12-01 13:54:45.483                                A

Col1        Col2                                                   Col3       
----------- ------------------------------------------------------ ---------- 
5           2004-12-01 13:54:45.517                                E
4           2004-12-01 13:54:45.500                                D
3           2004-12-01 13:54:45.500                                C
2           2004-12-01 13:54:45.500                                B
1           2004-12-01 13:54:45.483                                A

Brett

8-)

capn_wayne
Starting Member

9 Posts

Posted - 2004-12-01 : 14:27:59

Thanks for the explanations and the help. Got plenty of reading yet to do...

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources