Order of the result issue in T-SQL

Question

Order of the result issue in T-SQL

kbhanu15

SSCarpal Tunnel

Points: 4498
More actions
June 17, 2014 at 3:50 am

#308259
HI All,
I have an issue while display the result in the required order.
please help me to get the required output.
Code :
USE tempdb
GO
IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL
DROP TABLE #VersionFormat_tbl
CREATE TABLE #VersionFormat_tbl
(
[FormatID] [smallint] NOT NULL,
[Description] [varchar](50) NULL,
[fileExtension] [varchar](10) NOT NULL,
[versionFormatTypeId] [tinyint] NOT NULL,
CONSTRAINT [PK_VersionFormat_tbl] PRIMARY KEY CLUSTERED
(
[FormatID] ASC
)
)
INSERT INTO #VersionFormat_tbl
( FormatID ,
Description ,
fileExtension ,
versionFormatTypeId
)
VALUES
(1,'Text','txt',1),
(2,'HTML','html',1),
(3,'XML','xml',1),
(4,'PDF','pdf',1),
(5,'Excel','xls',1),
(6,'Word','doc',1)
GO
SELECT fileExtension,* FROM #VersionFormat_tbl
Present output :
Required output:
let me know for more details.
Thanks
Bhanu

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

How is the order you want being decided? They appear essentially random to me.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Easiest way will be to add another column, call it DisplayOrder or something like that. Populate that column with the order you want the rows to be displayed and ORDER BY that column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 3

yes you are right Gail Shaw, the suggestion you proposed is working fine.

thank you.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

kbhanu15 (6/17/2014)
yes you are right Gail Shaw, the suggestion you proposed is working fine.
thank you.

Can you post your solution please kbhanu15? It would be very handy for other folks who may stumble upon this thread.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 5

sure, please find the solution for this issue.

FIX 1:

USE tempdb

GO

IF OBJECT_ID('tempdb..#VersionFormat_tb') IS NOT NULL

DROP TABLE #VersionFormat_tbl

CREATE TABLE #VersionFormat_tbl

(

[FormatID] [smallint] NOT NULL,

[Description] [varchar](50) NULL,

[fileExtension] [varchar](10) NOT NULL,

[versionFormatTypeId] [tinyint] NOT NULL,

DisplayOrder SMALLINT NOT NULL

CONSTRAINT [PK_VersionFormat_tbl] PRIMARY KEY CLUSTERED

(

[FormatID] ASC

)

INSERT INTO #VersionFormat_tbl

( FormatID ,

Description ,

fileExtension ,

versionFormatTypeId,

DisplayOrder

)

VALUES

(1,'Text','txt',1,5),

(2,'HTML','html',1,1),

(3,'XML','xml',1,6),

(4,'PDF','pdf',1,4),

(5,'Excel','xls',1,2),

(6,'Word','doc',1,3)

GO

SELECT * FROM #VersionFormat_tbl

ORDER BY DisplayOrder