Board index » delphi » SQL Statement help needed!!!!!!

SQL Statement help needed!!!!!!

Hi there,

I'm fairly new to SQL and I figured most out already but now i've got a prob and i need
some help!!! (I'm using Delphi 3 Standard)

here's the prob:
I'm writing a program that uses a database that's divided into two major categories: LP
and LG, these categories are subdivided into: H, A, B, C, D and E ..The highest class is H
and the Lowest E.
Now i want to make a grouped report with QReport and I want the H class to be on the top
of the page and the E class on the bottom (with A to D in the middle)...

I tried everything i know about SQL but with the statements i used either the sequence
came out as A,B,C,D,E,H or H,E,D,C,B,A ...Can anybody tell me how the correct statement
must be?????????

Please reply by E-Mail.... thx.

TIA,
Arjen Wiersma

wier...@tref.nl

 

Re:SQL Statement help needed!!!!!!


I think you have a problem - but maybe others no better
The only way I can see is by using a secondary table with two fields ref and
pos, where
ref = h,a,b,c,d,e and pos 0,1,2,3,4

then use a joined query ordered by pos, in effect giving an output in the
order you require

hope it helps

John Perrett

Quote
Arjen Wiersma wrote in message <647ueg$g...@eubuf21.eu.concert.net>...
>I'm writing a program that uses a database that's divided into two major
categories: LP
>and LG, these categories are subdivided into: H, A, B, C, D and E ..The
highest class is H
>and the Lowest E.
>Now i want to make a grouped report with QReport and I want the H class to
be on the top
>of the page and the E class on the bottom (with A to D in the middle)...

>TIA,
>Arjen Wiersma

>wier...@tref.nl

Re:SQL Statement help needed!!!!!!


or,  alternatively , add a field 'pos' to the underlying data of type
fkInternalCalc with Oncalcfields procedure for the dataset to calculate the
pos field value so that the query can be ordered as you wish

John.Perr...@dial.pipex.com

Re:SQL Statement help needed!!!!!!


one last alternative ...

create separate queries for each category where you select only the records in
 that category, and manually add a field to sort on.  
then use the SQL statement  UNION  statement to bring these all together.  

This results in a large SQL statement, but for small tables doesn't execute
 much slower.  In essessence, this is what a JOIN does anyway ... albeit more
 efficiently.

for example:

SELECT  *, "0" SortField FROM SomeDatabase
WHERE  ref = "H"
UNION
SELECT   *, "1" SortField FROM SomeDatabase
WHERE  ref = "A"
....

ORDER BY SortField

M. Scott St.Cyr
Cyrious Software, Inc.
//***************************************************************//

Other Threads