[SQL] Custom Order By

Question

0.00/5 (No votes)

See more:

I have the following SQL Query:

SQL

SELECT        COUNT(*) AS Expr1
FROM            _Events AS t1 INNER JOIN
                         _EventToClient AS t2 ON t1.EventID = t2.EventID INNER JOIN
                         _Clients AS t3 ON t2.ClientID = t3.ClientID
WHERE        (t3.ClientConnected = 1) AND (t1.EventID = @some_id)

I would like to SELECT the top from the table _Events, while sorted in a descending manner according to this sorting key. What should I do?

SQL

SELECT TOP (@MaxAmount) ... FROM _Events WHERE ... ORDER BY ...

EDIT:
Is there a better way to do what I need than, the problem is that I can't select non GROUPED BY data...

SQL

SELECT        t1.EventID, t1., ..., COUNT(*) AS Key
FROM            _Events AS t1 INNER JOIN
                         _EventToClient AS t2 ON t1.EventID = t2.EventID INNER JOIN
                         _Clients AS t3 ON t2.ClientID = t3.ClientID
WHERE        (t3.ClientConnected = 1)
GROUP BY t1.EventID
ORDER BY Key DESC

Posted 28-Oct-11 0:44am

ShacharK

Updated 28-Oct-11 1:16am

v8

Add a Solution

3 solutions

Solution 1

by using veiw, you can solve your problem..

Posted 28-Oct-11 1:03am

sugumaran srinuvasan

Comments

ShacharK 28-Oct-11 7:15am

Could you please be more specific? Should I CREATE the VIEW within the procedure and then delete it?

Solution 3

Not tested. This might help:

SQL

SELECT _Events.*, cte.key
FROM _Events
INNER JOIN (
    SELECT EventID, MAX(Amount) AS MAmount, COUNT(*) AS key
    FROM _Events
    GROUP BY EventID
) AS cte
ON _Events.EventID = cte.EventID

Posted 28-Oct-11 2:56am

Corporal Agarn

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sugumaran srinuvasan · Accepted Answer · 2011-10-28T01:27:00

Solution 2

create view viewname

C#

as
SELECT        t1.EventID, t1., ..., COUNT(*) AS Key
FROM            _Events AS t1 INNER JOIN
                         _EventToClient AS t2 ON t1.EventID = t2.EventID INNER JOIN
                         _Clients AS t3 ON t2.ClientID = t3.ClientID
WHERE        (t3.ClientConnected = 1)
GROUP BY t1.EventID

then call tha view by

SQL

select * from viewname order by desc

Posted 28-Oct-11 1:27am

sugumaran srinuvasan

Updated 28-Oct-11 2:26am

v3

Comments

ShacharK 28-Oct-11 8:00am

Yeah I read about it, but should I CREATE the view every time? or should I make it permanent? I need high-performance in this query. Using a view would be more efficient, than selecting the t1.EventID, and then extracting the rest of the data?

sugumaran srinuvasan 28-Oct-11 8:31am

yeah... if the view table is created once, we can call when ever necessary.. it also pre compiled one. so it is fast and more efficient.

ShacharK 28-Oct-11 10:04am

Well, Thanks, it seems really useful, but I chose implementing a naive solution (extracting the IDs, followed by the rest of the data...), because of C# reasons (I have a database generator, and I don't want to expand it to support views as well...)

Thanks again :)

sugumaran srinuvasan 28-Oct-11 10:17am

its ok man.....