April 1, 2011 at 2:40 am
Hi,
Below is create table script
create table abc_test
(num_key int,
name nvarchar(20))
Insert into records script
insert into abc_test VALUES (1,'RIBI')
go
insert into abc_test VALUES (2,'GB')
go
insert into abc_test VALUES (3,'TRF')
go
insert into abc_test VALUES (4,'Charter Member')
go
insert into abc_test VALUES (5,'Pol')
go
insert into abc_test VALUES (6,'Dist')
go
insert into abc_test VALUES (7,'Honorary Member')
go
insert into abc_test VALUES (8,'Rot')
go
insert into abc_test VALUES (9,'Member')
go
insert into abc_test VALUES (10,'Clb')
go
I have to show the result from the below
SELECT * FROM abc_test
order by name
The first records should be as below
num_key name
1 Member
2 Honorary Member
3 Charter Member
and other records should be sorted by name.
Please provide me the solution that how to show above output.
I have to impliment this logic in my original SQL.
Regards,
Kiran R
April 1, 2011 at 2:49 am
But Kiran, Num_key values are different in your expected result and original table value. Do you want to replace it with 1, 2, and 3?
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 1, 2011 at 2:55 am
yes I want only the result start with,
Member has key 1, Honorary Member has key 2, Charter Member has key 3, and the other officer roles are sorted by name.
Thanks,
Kiran
April 1, 2011 at 3:08 am
Ok. So, My doubt is what should be the num_key value of these 3 names? 'RIBI', 'GB', and 'TRF'
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 1, 2011 at 3:12 am
It should be anything but I have to show num_key values for first three records are
Member has key 1,
Honorary Member has key 2,
Charter Member has key 3,
and the other records should be are sorted by name means alphabetical in order.
April 1, 2011 at 3:37 am
There might syntax errors in the below code as at present SQL is not installed in my system. But just try the below script after fixing the syntax errors.
WITH CTE_First(Num_key, Name)
(
SELECT 1, Name FROM abc_test WHERE num_key = 9
UNION ALL
SELECT 2, Name FROM abc_test WHERE num_key = 7
UNION ALL
SELECT 3, Name FROM abc_test WHERE num_key = 4
);
SELECT num_key, name FROM CTE_First
UNION ALL
SELECT num_key, name FROM abc_test
WHERE name NOT EXISTS(SELECT name FROM CTE_First)
ORDER BY name
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 1, 2011 at 7:19 am
WITH CTE_First(Num_key, Name) AS
(
SELECT 1, Name FROM abc_test WHERE num_key = 9
UNION ALL
SELECT 2, Name FROM abc_test WHERE num_key = 7
UNION ALL
SELECT 3, Name FROM abc_test WHERE num_key = 4
)
SELECT num_key, name FROM CTE_First
UNION ALL
SELECT num_key, name FROM abc_test
WHERE NOT EXISTS(SELECT name FROM CTE_First)
ORDER BY name
This sql only giving me three records as I expected but I want remaing records below that but In order by.
April 1, 2011 at 7:31 am
Try the below script.
WITH CTE_First(Num_key, Name) AS
(
SELECT 1, Name FROM abc_test WHERE num_key = 9
UNION ALL
SELECT 2, Name FROM abc_test WHERE num_key = 7
UNION ALL
SELECT 3, Name FROM abc_test WHERE num_key = 4
)
SELECT num_key, name FROM CTE_First
UNION ALL
SELECT num_key, name FROM abc_test
WHERE name NOT IN(SELECT name FROM CTE_First)
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 1, 2011 at 7:33 am
Hi,
Its giving me the result as below,
num_keyname
1Member
2Honorary Member
3Charter Member
1RIBI
2GB
3TRF
5Pol
6Dist
8Rot
10Clb
but I want the output as like below,
I don't want num_key in order by but name should me in order by
num_keyname
3Charter Member
4Clb
5Dist
6GB
2Honorary Member
1Member
7Pol
8Rot
9RIBI
10TRF
April 1, 2011 at 8:04 am
Try this
----------
WITH CTE_First(Num_key, Name) AS
(
SELECT 1, Name FROM abc_test WHERE num_key = 9
UNION ALL
SELECT 2, Name FROM abc_test WHERE num_key = 7
UNION ALL
SELECT 3, Name FROM abc_test WHERE num_key = 4
)
SELECT num_key, name FROM(
SELECT num_key, name FROM CTE_First
UNION ALL
SELECT num_key, name FROM abc_test
WHERE name NOT IN(SELECT name FROM CTE_First)) AS TBL
ORDER BY name
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 1, 2011 at 8:43 am
Thanks for your valuable help its fine for me:-)
April 1, 2011 at 8:48 am
No matter how you slice and dice this you are going to have some challenges. You are essentially trying to get a partially sorted result and this just simple isn't going to be possible. You are going to have devise a sort order using the rownumber function and a temp table. The num_key field is totally useless in this example because you are going to have to hard code the first 3 values which means the output is NOT that field.
I think is what you are looking for
declare @Result table (SortOrder int, name nvarchar(20))
--first insert the hard coded top 3
insert @Result
select 1, name from abc_test where num_key = 9
union all
select 2, name from abc_test where num_key = 7
union all
select 3, name from abc_test where num_key = 4
--now insert the rest of them, these need to be ordered correctly so the SortOrder will be in sequence
insert @Result
select RANK() over (order by name) + 3 as RowNum, name
from abc_test
where num_key not in (9,7,4)
order by RowNum
select * from @Result order by SortOrder
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 1, 2011 at 9:09 am
Thanks a lot its working...:-)
April 1, 2011 at 9:17 am
One more thing...
If you are sure the first 3 names are static you can replace the select query as below. No need to hit the database to get that data.
SELECT 1, 'Member'
UNION ALL
SELECT 2, 'Honorary Member'
UNION ALL
SELECT 3, 'Charter Member'
_____________________________________________
One ounce of practice is more important than tonnes of dreams
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply