We help IT Professionals succeed at work.

sql

JCWEBHOST
JCWEBHOST asked
on
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.
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
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?
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you won't put the WHERE clause, as you want the other records also
SELECT * 
  FROM PROJECTS 
ORDER BY CASE WHEN ID = " + id + " THEN 0 ELSE 1 END ASC, ID DESC

Open in new window

Kiran SonawaneProject Lead
Top Expert 2011

Commented:
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
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

Author

Commented:
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

Author

Commented:
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 SonawaneProject Lead
Top Expert 2011

Commented:
Try like this

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

union

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

Author

Commented:
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 SonawaneProject Lead
Top Expert 2011

Commented:
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";

Author

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

Author

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

Author

Commented:
works great thanks