Re: order by clause - Database Discussion Boards

venkat11612-Apr-11 1:47

12-Apr-11 1:47

Hi All,

I have one table containing few columns.
LruName, ChName and Type.

I want to fetch a query from the above table
order by LruName, ChName, Type

by using the above query I am getting output like this
-------------------------------------------------------------------------------------------------------
LruName ChName Type
ADU1 CH1 Tx
ADU2 CH2 Tx
AHRS1 CH3 Tx
AHRS2 CH4 Tx
DVS CH24 Rx
DVS CH28 Rx
DVS CH5 Tx
DVS CH6 Tx
FADEC1 CH1 Tx
FADEC1 CH2 Tx
FADEC2 CH3 Tx
FADEC2 CH4 Tx
NLDW CH8 Tx
OAS CH7 Tx
----------------------------------------------------------------------------------------------------------

But I want output like this

----------------------------------------------------------------------------------------------------------
LruName ChName Type
ADU1 CH1 Tx
ADU2 CH2 Tx
AHRS1 CH3 Tx
AHRS2 CH4 Tx
DVS CH5 Tx
DVS CH6 Tx
DVS CH24 Rx
DVS CH28 Rx
FADEC1 CH1 Tx
FADEC1 CH2 Tx
FADEC2 CH3 Tx
FADEC2 CH4 Tx
NLDW CH8 Tx
OAS CH7 Tx

ie I want LruName, Chname and Type to be order wise
again in Type Tx ( rows ) should come first then Rx.

how to write a query by getting the above results.

Re: order by clause

Luc Pattyn12-Apr-11 1:56

Luc Pattyn

12-Apr-11 1:56

if the Type field is limited to the values in your example, then a descending order would do it, hence:

order by LruName, ChName, Type DESC

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Re: order by clause

venkat11612-Apr-11 2:03

venkat116

12-Apr-11 2:03

Hi,
thank u for the reply.
But i want Tx rows should come first then Rx rows

Re: order by clause

Luc Pattyn12-Apr-11 2:10

Luc Pattyn

12-Apr-11 2:10

as Tx > Rx and you want Tx first, descending should do it.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Re: order by clause

venkat11612-Apr-11 2:23

venkat116

12-Apr-11 2:23

It is not working
I gave like this
ORDER BY LruName, ChName, Type desc

Re: order by clause

Luc Pattyn12-Apr-11 2:34

Luc Pattyn

12-Apr-11 2:34

in your example the Type isn't really relevant to the sort as all (LruName,ChName) combinations are unique already without the Type field.

So maybe you want

order by LruName, Type DESC, ChName

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Re: order by clause

venkat11612-Apr-11 2:40

venkat116

12-Apr-11 2:40

yaah... it is working

I got what I want

thank u very much.

Re: order by clause

Chris Meech12-Apr-11 3:02

Chris Meech

12-Apr-11 3:02

One of the problems you are going to have is with the ChName column. It appears as though you want CH5 to appear before CH24. In your order by clause you may have to have it

order by .... ,length(ChName),ChName, ...

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Re: order by clause

venkat11612-Apr-11 3:47

venkat116

12-Apr-11 3:47

Hi,

Luc Pattyn
solved my problem

thank you for the reply

Re: order by clause

Jörgen Andersson12-Apr-11 9:31

Jörgen Andersson

12-Apr-11 9:31

That's a good solution for a common problem.
A five from me even if it wasn't what the OP wanted.

List of common misconceptions

Re: order by clause

Chris Meech12-Apr-11 9:43

Chris Meech

12-Apr-11 9:43

Thanks Jörgen.

Jörgen Andersson wrote:
even if it wasn't what the OP wanted.

Oddly, in the original post he indicated what output he was after and so I'm pretty sure he is going to have to do something similar to what I offered.

Re: order by clause

Jörgen Andersson12-Apr-11 9:46

Jörgen Andersson

12-Apr-11 9:46

I thought so too, and was contemplating looking up a stringsplit function that does the same job in a much more tortuous way.

List of common misconceptions

Re: order by clause

David Skelly12-Apr-11 22:23

David Skelly

12-Apr-11 22:23

I have a feeling it's only going to work if all the name values start with the same prefix.

Supposing I have:

A1
A20
A999
C1
C99

Sorting them by length(name), name would give:

A1
C1
A20
C99
A999

So it would solve the OP's specific problem where all names appear to start with CH but I'm not sure it works as a general solution.

Re: order by clause

Jörgen Andersson12-Apr-11 23:06

Jörgen Andersson

12-Apr-11 23:06

I believe you're quite correct.

We have exactly this issue and have solved it with a separate sortcolumn.
But this isn't always a viable solution, and then one has to build a natural sort function[^].

List of common misconceptions

Re: order by clause

Prasanta_Prince13-Apr-11 1:31

Prasanta_Prince

13-Apr-11 1:31

just the following,-
order by LruName, ChName, Type DESC

Dealing with apostrophe in stored procedure

berba11-Apr-11 22:15

berba

11-Apr-11 22:15

When a variable in my stored procedure contains an apostrophe, it returns me an error.

I've tried the function REPLACE() but no luck.

waiting for your suggesions.

Thanks

Re: Dealing with apostrophe in stored procedure

Eddy Vluggen12-Apr-11 0:02

Eddy Vluggen

12-Apr-11 0:02

berba wrote:
I've tried the function REPLACE() but no luck.

waiting for your suggesions.

I suggest you post the error-message that you got when you tried that function, or the code that you tried.

I are Troll Suspicious | :suss:

Re: Dealing with apostrophe in stored procedure

Chris Meech12-Apr-11 2:52

Chris Meech

12-Apr-11 2:52

Without seeing the code that uses the variable and how the variable is applied to the code, it's difficult to know what is happening. And also knowing what the error return is will help out as well.

Just a guess, but usually embedded apostrophes within strings will need to be escaped somehow. Smile | :)

Re: Dealing with apostrophe in stored procedure

thatraja12-Apr-11 7:23

thatraja

12-Apr-11 7:23

You must post your query in your question.

Replace single quote in sql server[^] BTW enjoy those brief explanations there

thatraja

My Tip/Tricks
My Dad had a Heart Attack on this day so don't...

Re: Dealing with apostrophe in stored procedure

berba12-Apr-11 21:35

berba

12-Apr-11 21:35

CREATE PROCEDURE sp_GetEZQueryList @@sUser as nchar(500), @@sSupervisor as char(1), @@sAllowed as varchar(8000) AS

--set @@sUser = REPLACE(@@sUser, '''''','''''')
IF @@sSupervisor <> 'Y'
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
WHERE (EzqUserName=@@sUser)
OR (EzqUserName='SYSTEM' AND Cast(EzqFlxID as varchar(10)) IN (select * FROM intlist_to_tbl(@@sAllowed)))
ELSE
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
GO

The SP is retrieving @@sUser from VB and its getting the value Do'Sullivan.
When I ran a trace I found it is executing the SP like this

exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100'

Think + CHAR(39) + is causing the error

Thanks

Re: Dealing with apostrophe in stored procedure

berba12-Apr-11 21:34

berba

12-Apr-11 21:34

Re: Dealing with apostrophe in stored procedure

berba14-Apr-11 20:38

berba

14-Apr-11 20:38

Any solutions ??

Cheers

Re: Dealing with apostrophe in stored procedure

S Douglas28-Apr-11 11:47

S Douglas

28-Apr-11 11:47

Little Debugging..

CREATE PROCEDURE sp_GetEZQueryList
(
       @sUser AS nchar(500) ,
       @sSupervisor AS char(1) ,
       @sAllowed AS varchar(8000)
)
AS 

IF @sSupervisor <> 'Y'
   SELECT
       EzqDateMod ,
       EzqFlxID ,
       EzqUserName ,
       EzqName ,
       EzqData ,
       EzqDescrip
   FROM
       EZQuery
   WHERE
       ( EzqUserName = @sUser ) 
		OR 
	   ( EzqUserName = 'SYSTEM' /*AND CAST(EzqFlxID AS varchar(10)) IN ( SELECT * FROM intlist_to_tbl WHERE(@sAllowed) ) */)

ELSE
   SELECT
       EzqDateMod ,
       EzqFlxID ,
       EzqUserName ,
       EzqName ,
       EzqData ,
       EzqDescrip
   FROM
       EZQuery
GO

exec sp_getezquerylist 'DO''SULLIVAN', 'N', '100'

Executes fine, without the IN clase to look up

The source of your issues appear to be in this function (I assume its a function based on the nomenclature) intlist_to_tbl. If intlist_to_tbl is really a function why are you sending that data to the db, and converting it instead of storing it in the db?

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

SSRS Problem [modified]

Kevin Marois10-Apr-11 10:32

Kevin Marois

10-Apr-11 10:32

I'm fairly new tyo SSRS, so bear with me...

I created a report that is getting its data from a stored procedure. When I run the stored procesure in the data tab it is returning 68 rows, yet only 1 rows shows up on the report in preview, and, it's the last row in the dataset.

If I export the data as a CSV to the desktop, there is only one row in the data.

I have no clue how to debug this. Could use some help.

Thanks

Everything makes sense in someone's mind

modified on Sunday, April 10, 2011 4:49 PM

Re: SSRS Problem

S Douglas13-Apr-11 11:50

S Douglas

13-Apr-11 11:50

I have run into this a couple of times, look for a {file name}.rdl.data file and delete it it. That contains a cached set of data for the report.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.