Order By Param

Is there a way to have a report parameter for the selection of what field
the user wants a table to be grouped on? For instance, the parameter would
have 3 options, clientid, clientname, and clientcity. I want to be able to
select one of these and have the table group on this selection.
Or any generic ways or ideas on how to accomplish this?
Thanks!!!I am looking for the same solution. I have a client that I have promised 10
reports to and they gave me a few but want sort capability on 5 different
fields. I could say that those are 5 different reports, but there should be
an easy way to list the fields in a drop down parameter and adjust the ORDER
BY based on this. Shouldn't there?
Thanks in advance.
ANDY
"Amon Borland" wrote:
> Is there a way to have a report parameter for the selection of what field
> the user wants a table to be grouped on? For instance, the parameter would
> have 3 options, clientid, clientname, and clientcity. I want to be able to
> select one of these and have the table group on this selection.
> Or any generic ways or ideas on how to accomplish this?
> Thanks!!!
>
>|||can you pass the field to sort on into the report as a parameter and use a
CASE in your query to conditionally sort based on whatever you passed in as
a parameter?
Bill
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:C80B4FC3-FA92-4ADE-BFEA-D6B0867FDE97@.microsoft.com...
> I am looking for the same solution. I have a client that I have promised
10
> reports to and they gave me a few but want sort capability on 5 different
> fields. I could say that those are 5 different reports, but there should
be
> an easy way to list the fields in a drop down parameter and adjust the
ORDER
> BY based on this. Shouldn't there?
> Thanks in advance.
> ANDY
> "Amon Borland" wrote:
> > Is there a way to have a report parameter for the selection of what
field
> > the user wants a table to be grouped on? For instance, the parameter
would
> > have 3 options, clientid, clientname, and clientcity. I want to be able
to
> > select one of these and have the table group on this selection.
> >
> > Or any generic ways or ideas on how to accomplish this?
> >
> > Thanks!!!
> >
> >
> >|||It seems all my replied are this..
Sorting is easy
DECLARE @.sql as varchar(100)
SET @.sql = 'SELECT * FROM blah WHERE blah = blah ORDER BY '
@.paramete
EXEC sp_executesql @.sq
Grouping on the other hand I don't think you can do dynamically

Order by "string not empty"?

Hi ng.

I have a varchar field in my table, called Name.

I wanna do a selection, which is ordered by whether this field is empty or
not.

E.g. something like:

SELECT
UserID
ORDER BY
Name <> '';

- - -

How can I accomplish this?

TIA.
Klaus.This will put the non-empty rows first:

SELECT userid
FROM SomeTable
ORDER BY name DESC

--
David Portas
SQL Server MVP
--|||> This will put the non-empty rows first:
> SELECT userid
> FROM SomeTable
> ORDER BY name DESC

It just doesnt seem to be a wise way to do it - why do string sorting when
the only thing needed is whether its empty or not...?|||On Tue, 8 Jun 2004 15:21:36 +0200, Klaus Petersen wrote:

>> This will put the non-empty rows first:
>>
>> SELECT userid
>> FROM SomeTable
>> ORDER BY name DESC
>It just doesnt seem to be a wise way to do it - why do string sorting when
>the only thing needed is whether its empty or not...?

Hi Klaus,

Maybe because ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 2 END is a lot
harder to read and maintain?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||In addition to Hugo's point, using a CASE expression would require that the
expression be evaluated for every single row. Compare performance of the two
approaches and see which works best for you.

--
David Portas
SQL Server MVP
--

ORDER BY - clause

Hi,
I have the following output from a SELECTion query.
Query: SELECT RollNo FROM Student
Output:
10
20
30
40
I want this result in following oder
20
40
10
30
Is it possible?
please advise me.
Thanks,
Soura.You need to specify why do you want that particular order? Is it ordered by
some other column or property?
MC
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:FDC26A6A-D5CC-4A41-817E-141D818D83F7@.microsoft.com...
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.|||SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
You can put the ordering in a table and use that in your query:
CREATE TABLE roll_order
(rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
INSERT INTO roll_order (rollno, sequenceno)
SELECT 20,1 UNION ALL
SELECT 40,2 UNION ALL
SELECT 10,3 UNION ALL
SELECT 30,4 ;
SELECT S.rollno
FROM student AS S
JOIN roll_order AS R
ON S.rollno = R.rollno
ORDER BY R.sequenceno ;
If rollno is unique in the student table then you don't need another
table - you can put the sequenceno in student instead.
If you prefer not to create an extra column then try this:
SELECT rollno
FROM student
ORDER BY
CASE rollno
WHEN 20 THEN 1
WHEN 40 THEN 2
WHEN 10 THEN 3
WHEN 30 THEN 4
END ;
Hope this helps.
David Portas
SQL Server MVP
--|||if you know the specific order you want it in you can do this in your order
statemetn
(CASE numbercolumn WHEN '20' THEN 0 WHEN '40' THEN 1 WHEN '10' THEN 2 else
4 END )
but you have to specifically give every result a ranking so it will order
correctly
Kelly
"David Portas" wrote:

> SouRa wrote:
>
> You can put the ordering in a table and use that in your query:
> CREATE TABLE roll_order
> (rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
> INSERT INTO roll_order (rollno, sequenceno)
> SELECT 20,1 UNION ALL
> SELECT 40,2 UNION ALL
> SELECT 10,3 UNION ALL
> SELECT 30,4 ;
> SELECT S.rollno
> FROM student AS S
> JOIN roll_order AS R
> ON S.rollno = R.rollno
> ORDER BY R.sequenceno ;
> If rollno is unique in the student table then you don't need another
> table - you can put the sequenceno in student instead.
> If you prefer not to create an extra column then try this:
> SELECT rollno
> FROM student
> ORDER BY
> CASE rollno
> WHEN 20 THEN 1
> WHEN 40 THEN 2
> WHEN 10 THEN 3
> WHEN 30 THEN 4
> END ;
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Yet another solution:
CREATE TABLE Student (RollNo int not null)
INSERT INTO Student VALUES (10)
INSERT INTO Student VALUES (20)
INSERT INTO Student VALUES (30)
INSERT INTO Student VALUES (40)
SELECT RollNo
FROM Student
ORDER BY RollNo%20, RollNo
-- DROP TABLE Student
HTH,
Gert-Jan
SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.sql

ORDER BY - clause

Hi,
I have the following output from a SELECTion query.
Query: SELECT RollNo FROM Student
Output:
10
20
30
40
I want this result in following oder
20
40
10
30
Is it possible?
please advise me.
Thanks,
Soura.
You need to specify why do you want that particular order? Is it ordered by
some other column or property?
MC
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:FDC26A6A-D5CC-4A41-817E-141D818D83F7@.microsoft.com...
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
|||SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
You can put the ordering in a table and use that in your query:
CREATE TABLE roll_order
(rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
INSERT INTO roll_order (rollno, sequenceno)
SELECT 20,1 UNION ALL
SELECT 40,2 UNION ALL
SELECT 10,3 UNION ALL
SELECT 30,4 ;
SELECT S.rollno
FROM student AS S
JOIN roll_order AS R
ON S.rollno = R.rollno
ORDER BY R.sequenceno ;
If rollno is unique in the student table then you don't need another
table - you can put the sequenceno in student instead.
If you prefer not to create an extra column then try this:
SELECT rollno
FROM student
ORDER BY
CASE rollno
WHEN 20 THEN 1
WHEN 40 THEN 2
WHEN 10 THEN 3
WHEN 30 THEN 4
END ;
Hope this helps.
David Portas
SQL Server MVP
|||if you know the specific order you want it in you can do this in your order
statemetn
(CASE numbercolumn WHEN '20' THEN 0 WHEN '40' THEN 1 WHEN '10' THEN 2 else
4 END )
but you have to specifically give every result a ranking so it will order
correctly
Kelly
"David Portas" wrote:

> SouRa wrote:
>
> You can put the ordering in a table and use that in your query:
> CREATE TABLE roll_order
> (rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
> INSERT INTO roll_order (rollno, sequenceno)
> SELECT 20,1 UNION ALL
> SELECT 40,2 UNION ALL
> SELECT 10,3 UNION ALL
> SELECT 30,4 ;
> SELECT S.rollno
> FROM student AS S
> JOIN roll_order AS R
> ON S.rollno = R.rollno
> ORDER BY R.sequenceno ;
> If rollno is unique in the student table then you don't need another
> table - you can put the sequenceno in student instead.
> If you prefer not to create an extra column then try this:
> SELECT rollno
> FROM student
> ORDER BY
> CASE rollno
> WHEN 20 THEN 1
> WHEN 40 THEN 2
> WHEN 10 THEN 3
> WHEN 30 THEN 4
> END ;
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
|||Yet another solution:
CREATE TABLE Student (RollNo int not null)
INSERT INTO Student VALUES (10)
INSERT INTO Student VALUES (20)
INSERT INTO Student VALUES (30)
INSERT INTO Student VALUES (40)
SELECT RollNo
FROM Student
ORDER BY RollNo%20, RollNo
-- DROP TABLE Student
HTH,
Gert-Jan
SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.

ORDER BY - clause

Hi,
I have the following output from a SELECTion query.
Query: SELECT RollNo FROM Student
Output:
10
20
30
40
I want this result in following oder
20
40
10
30
Is it possible?
please advise me.
Thanks,
Soura.You need to specify why do you want that particular order? Is it ordered by
some other column or property?
MC
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:FDC26A6A-D5CC-4A41-817E-141D818D83F7@.microsoft.com...
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.|||SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
You can put the ordering in a table and use that in your query:
CREATE TABLE roll_order
(rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
INSERT INTO roll_order (rollno, sequenceno)
SELECT 20,1 UNION ALL
SELECT 40,2 UNION ALL
SELECT 10,3 UNION ALL
SELECT 30,4 ;
SELECT S.rollno
FROM student AS S
JOIN roll_order AS R
ON S.rollno = R.rollno
ORDER BY R.sequenceno ;
If rollno is unique in the student table then you don't need another
table - you can put the sequenceno in student instead.
If you prefer not to create an extra column then try this:
SELECT rollno
FROM student
ORDER BY
CASE rollno
WHEN 20 THEN 1
WHEN 40 THEN 2
WHEN 10 THEN 3
WHEN 30 THEN 4
END ;
Hope this helps.
--
David Portas
SQL Server MVP
--|||if you know the specific order you want it in you can do this in your order
statemetn
(CASE numbercolumn WHEN '20' THEN 0 WHEN '40' THEN 1 WHEN '10' THEN 2 else
4 END )
but you have to specifically give every result a ranking so it will order
correctly
Kelly
"David Portas" wrote:
> SouRa wrote:
> > Hi,
> >
> > I have the following output from a SELECTion query.
> >
> > Query: SELECT RollNo FROM Student
> > Output:
> >
> > 10
> > 20
> > 30
> > 40
> >
> > I want this result in following oder
> >
> > 20
> > 40
> > 10
> > 30
> >
> > Is it possible?
> > please advise me.
> >
> > Thanks,
> > Soura.
>
> You can put the ordering in a table and use that in your query:
> CREATE TABLE roll_order
> (rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
> INSERT INTO roll_order (rollno, sequenceno)
> SELECT 20,1 UNION ALL
> SELECT 40,2 UNION ALL
> SELECT 10,3 UNION ALL
> SELECT 30,4 ;
> SELECT S.rollno
> FROM student AS S
> JOIN roll_order AS R
> ON S.rollno = R.rollno
> ORDER BY R.sequenceno ;
> If rollno is unique in the student table then you don't need another
> table - you can put the sequenceno in student instead.
> If you prefer not to create an extra column then try this:
> SELECT rollno
> FROM student
> ORDER BY
> CASE rollno
> WHEN 20 THEN 1
> WHEN 40 THEN 2
> WHEN 10 THEN 3
> WHEN 30 THEN 4
> END ;
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Yet another solution:
CREATE TABLE Student (RollNo int not null)
INSERT INTO Student VALUES (10)
INSERT INTO Student VALUES (20)
INSERT INTO Student VALUES (30)
INSERT INTO Student VALUES (40)
SELECT RollNo
FROM Student
ORDER BY RollNo%20, RollNo
-- DROP TABLE Student
HTH,
Gert-Jan
SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.