asked on

sql

Hey guys i need a sql statement that will select the last record by desc on id and than join the rest of the items.

e.g my table: projects

id name

1 pj1
2 pj2
3 pj3

my select statement must select my records like this:

id name
3 pj3
2 pj2
1 pj1

i also need a statement if i give the id e.g 2 it must select 2 then add the rest of the items

id name
2 pj2
3 pj3
1 pj1

i want to bring the selected item on top of my menu.

thanks.

Guy Hengel [angelIII / a3]

the first part is doing
ORDER BY id desc

for the second part:
ORDER BY CASE WHEN ID = 2 THEN 0 ELSE 1 END ASC, ID DESC

JCWEBHOST

ASKER

i have this sql statment

Select * from projects where id='" + id + "' ORDER BY id DESC

now this just set by desc does not put the id first than order by?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kiran Sonawane

You can do this using union

Select * from projects where id='" + id + "' ORDER BY id DESC

union

Select * from projects where id not in (id) ORDER BY id DESC

santhimurthyd

Case I

Select * from projects where id=" + id + " ORDER BY id DESC

Case II

Create table #tempprojects
(
id int,
name varchar(20)
)

insert into #tempprojects
select * from projects where id = 2

insert into #tempprojects
select * from projects where id <> 2 ORDER by id desc

select * from #tempprojects

Drop table #tempprojects

JCWEBHOST

ASKER

incorrect syntax by union

Select * from projects where id='" + id + "' ORDER BY id DESC

union

Select * from projects where id not in (id) ORDER BY id DESC

JCWEBHOST

ASKER

My SQL Query

Select * from projects where id='3' ORDER BY id DESC UNION Select * from projects where id not in ('3') ORDER BY id DESC

Erorr when i do a trace

Incorrect syntax near the keyword 'UNION'.

Kiran Sonawane

Try like this

Select * from projects where id='" + id + "'

union

Select * from projects where id not in (id) ORDER BY id DESC

JCWEBHOST

ASKER

only selecting the select one the the query

public DataTable return_project_menu(string id)
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connStr;
DataTable dt = new DataTable();

try
{
connection.Open();
string sq = "Select id, SUBSTRING(title, 0 , 20) + '...' as 'short_title', title from projects where id='" + id + "' union Select id, SUBSTRING(title, 0 , 20) + '...' as 'short_title', title from projects where id not in (id) ORDER BY id DESC";
SqlCommand cm = new SqlCommand(sq, connection);

dt.Load(cm.ExecuteReader());
}
catch (Exception)
{ }
finally
{
connection.Close();
}

return dt;
}

Guy Hengel [angelIII / a3]

the issue is that with UNION , the ORDER BY applies AFTER the union, so it won't help.

please try my suggestion with the order by case ...

Kiran Sonawane

Didn't get what you mean. You may need to do below if I am correct

Try by replacing below line

string sq = "Select id, SUBSTRING(title, 0 , 20) + '...' as 'short_title', title from projects where id='" + id + "' union Select id, SUBSTRING(title, 0 , 20) + '...' as 'short_title', title from projects where id not in (id) ORDER BY id DESC";

with

string sq = "Select id, SUBSTRING(title, 0 , 20) + '...' as 'short_title', title from projects where id='" + id + "' union Select id, SUBSTRING(title, 0 , 20) + '...' as 'short_title', title from projects where id not in ('" + id + "' ) ORDER BY id DESC";

JCWEBHOST

ASKER

it works but it never sort my menu and put the id on top?

JCWEBHOST

ASKER

it still display my records in desc and never put my select id on top?

JCWEBHOST

ASKER

works great thanks