October 16, 2009 at 12:14 am
Hi
as iam new to T-sql plzz help me hw to write query
suppose there are 10 records in a table i need to display 6th record on top and rest in same order
eg i have table
ename salary
---------- --------
abc 2000
asd 1000
azz 5000
awe 4000
qwe 6000
rew 3000
bbc 9000
;;10 records
my output shld be as follows
ename salary
---------- --------
rew 3000
abc 2000
asd 1000
azz 5000
awe 4000
qwe 6000
bbc 9000
can anybody help hw to write query????????/
October 16, 2009 at 1:01 am
You need to give more information if you want help, is it always going to be the 6th record or can it be any record and why is it that record, what in your table would be used to uniquely identify the record that you want displayed at the top.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 16, 2009 at 1:15 am
Also , and im trying not to be Celko here :-), there is no such thing as the 6'th record.
Do not make the assumption that a table can be used as a neatly ordered list of rows.
The only thing that will guarantee order is the "order by" statement.
With that said, I see no way that your "6'th record" will ever being ordered into the 6'th returned row.
October 16, 2009 at 2:59 am
thanxx fr replying
i just want a query to display 6th records as first and rest in order
October 16, 2009 at 3:08 am
October 16, 2009 at 3:29 am
Dave Ballantyne (10/16/2009)
So have you :a) Not understood what ive said
b) Ignored what ive said
i have understood wat u hav said assume that record are in order and nw i want to display 6th record as first and rest in order is it possible r not? as iam new to t-sql --kindly help me
October 16, 2009 at 3:40 am
Well you clearly havent, as you are still saying 6'th record.
What order by clause are you using to make it the 6'th row in your result set.
Please note the massive conceptual difference of "6'th record" and "6'th row in a result set"
October 16, 2009 at 3:44 am
tabrez.test (10/16/2009)
Dave Ballantyne (10/16/2009)
So have you :a) Not understood what ive said
b) Ignored what ive said
i have understood wat u hav said assume that record are in order and nw i want to display 6th record as first and rest in order is it possible r not? as iam new to t-sql --kindly help me
As you are new to t-sql, we are trying to help. you say that they are in order. what order are they in, as your example doesnt look ordered.
there are ways of doing what you want, but they are complicated. can you explain why or what you are trying to achieve and we can see if there is a different way of doing this.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 16, 2009 at 3:48 am
ok u can take it as 6th row should be displayed first n rest in order(order means as they are entered in database)
October 16, 2009 at 4:01 am
Ok , heres an analogy for you.
Image that each rows data is written on a piece of paper then thrown into a bucket.
Now , you are asking for the sixth piece of paper that went into the bucket.
Have you record on the piece of paper which order they went into the bucket ?
From the data provided, no !
Its impossible to tell.
October 16, 2009 at 4:03 am
ok...
this table that these records are in, is there an index on this table?
if you run a SELECT query on this table, do you use a ORDER BY clause?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 16, 2009 at 4:11 am
tabrez.test (10/16/2009)
(order means as they are entered in database)
So you've got a column that stores the datetime that the row was inserted? Or an identity column?
If you have neither, there is no way whatsoever to tell what order the rows were inserted into the table. You need some column to order by.
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
October 16, 2009 at 4:13 am
You will need to order the data by something. In this example I created a unique id using the row_number function by ordering on column b.
-- Setup test table & data
set nocount on
declare @a table (b varchar(128))
insert into @a values ('4t43r3w')
insert into @a values ('dvsdfsg')
insert into @a values ('ygfdbns3')
insert into @a values ('fnnuj')
insert into @a values ('g456gf')
insert into @a values ('vsvresv')
insert into @a values ('rverwe')
insert into @a values ('bwsvwz')
insert into @a values ('bvefzcww')
insert into @a values ('vevwc')
-- retrieve normal order
select ROW_NUMBER() over (order by b)
as row, B from @a
order by ROW_NUMBER() over (order by b)
-- retrieve record 6 first, then the rest
select case when ROW_NUMBER() over (order by b) = 6 then 0 else
ROW_NUMBER() over (order by b) end
as row, B from @a
order by case when ROW_NUMBER() over (order by b) = 6 then 0 else
ROW_NUMBER() over (order by b) end
Steven
October 16, 2009 at 4:16 am
ok take this as example
ill explain here in a better way here order is asc on sal
name sal
aaa 1000
bbb 2000
ccc 3000
ddd 4000
eee 5000
is there any query to get the below result
out put should be like below
ddd 4000
aaa 1000
bbb 2000
ccc 3000
eee 5000
October 16, 2009 at 4:22 am
Ok , now you've defined an order things get much easier....
Steven has give you the answer above.....
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply