Display Query Results in Order

[k09]

Hi,

I want to display the event field based on the value of @securityID.
After that I want to display all event fields in the database.

How do I do this? Belwo is my code...

SELECT     e.eventID, e.event
FROM         tble AS e LEFT OUTER JOIN
                      tbld AS d ON d.eventID = e.eventID
ORDER BY 
                          (SELECT     d2.securityID
                            FROM          tbld AS d2 INNER JOIN
                                                   tble AS e2 ON e2.eventID = d2.eventID
                            WHERE      (d2.securityID = @securityID))

Thanks.

[r937]

SELECT e.eventID
     , e.event
  FROM tble AS e 
LEFT OUTER 
  JOIN tbld AS d 
    ON d.eventID = e.eventID
ORDER 
    BY case when e.securityID = @securityID
            then 'sort these first'
            else 'sort these last'
       end
     , e.eventID

[k09]

SQL Server gives the error...

"Parameter is Incorrect"

[r937]

does the query work if you do this --

ORDER 
    BY e.eventID

[k09]

put it in a stored procedure and works ok.

However, if rpws from tbld match the same rows from tble then tble rows are displayed multiple times.
Tried SELECT DISTINCT but doesn't work with ORDER BY in SQL Server

[r937]

yes, SELECT DISTINCT does work with ORDER BY in SQL Server

however, SELECT DISTINCT will not resolve a one-to-many "problem"

perhaps you could show us your latest query

[k09]

Hi,

Here is my query...

    SELECT e.eventID, e.event
    FROM tble AS e 
    LEFT OUTER JOIN
    tbld AS d 
    ON d.eventID = e.eventID
    ORDER BY CASE WHEN d.securityID = @securityID 
    THEN 'sort these first' ELSE 'sort these last' END, e.eventID

[r937]

try replacing WHEN d.securityID = @securityID with WHEN e.securityID = @securityID

[r937]

by the way, for what reason are you doing a self-join?

[k09]

Hi,

e.g. My table has 3 rows with same ID and want them all displayed in textBox...

event eventID
----------------- ---------------
itemA 2
itemB 2
itemC 2

so the textBox should display itemA,itemB,itemC

Thank You

[k09]

e.securityID does not exist. securityID only in tbld

also tried...

SELECT CONCAT(e.event) AS myColumn FROM tble

but expects another value.

[r937]

please, don't confuse me, stick with eventID and event, not myField and ID

to display 3 rows with the same id, just select them

[k09]

Sorry Rudy!
I've been confusing myself with different stored procedures.

My answer to post#9 is incorrect.
Here is what I want...

tble and tbld are related via eventID
tbld can contain the same eventID for each row.
So I use a JOIN to display the event based on @securityID value.
Then I want to display all event fields in the tble database table.

Problem is that, because tbld contains multiple references to the one eventID in tble, the same event is displayed a number f times.

How do I prevent this??

Thanks and sorry for confusing the both of us!

[r937]

okay, i think i finally understand

SELECT e.eventID
     , e.event
     , MIN( CASE WHEN d.securityID = @securityID 
                 THEN 'sort these first' 
                 ELSE 'sort these last' 
             END ) as sortkey
  FROM tble AS e 
LEFT OUTER 
  JOIN tbld AS d 
    ON d.eventID = e.eventID
GROUP
    BY e.eventID
     , e.event
ORDER 
    BY sortkey
     , e.eventID

[k09]

Great! That works.

Could you please explain how 'sort these first' works? Is that actual SQL code?

Thank You.

[r937]

i was wondering if you'd notice

actually it could be anything

evaluate the CASE expression for every d row, and it will produce one value or the other

then, in the GROUP BY, take the minimum of all the values for each group, i.e. for each e row

if one of the d.securityID values was equal to @securityID, then the MIN will equal the THEN value, simply because the THEN value is less than the ELSE value

so all the events that had at least one @securityID row will have the lower value in the sortkey column, and therefore be sorted first

why 'sort these first' and 'sort these last' as values? because "f" comes before "l"