Link to home
Start Free TrialLog in
Avatar of rares_dumitrescu

asked on

SQL Advanced SELECT Statement


I have 2 tables like the following:


name    properties
xxx           file1
xxx           file2
yyy           file3
yyy           file2
yyy           file1

I need a select statement that will prompt me the following output:

1. xxx    file1
             file2        --this value needs to be exactly like this, on the next line

2. yyy   file3

Avatar of chapmandew
Flag of United States of America image

select * from table2
Avatar of rares_dumitrescu



I think you got the wrong picture.

Let`s say I have only table 2

I need a select that will prompt me:

column1  column2  column3
1.             xxx            file1
2.             yyy            file3
You need to define which row is the first one in each group.  I presume you're implying to use the first one from each name but you can't count on ordering like that in a relational database.  If you were to say, use the first one from each name alphabetically it would be something like this

      when = t3.themax then name
      else Space(Len( End As name2,
  From table2 as t2 Join (
      Select name, max(properties) as themax
        from table2
        group by name ) As t3
    On =
nothing in sql can guarantee you that output ...

you appear to want the xxx rows in ascending order of
properties file1 then file2
whilst for the yyy rows you want the descending property order
file3 then file 2 then file1

you haven't indicated any other relationships which would allow the
data to be generically ordered in a consistent manner...

explain why you have the two tables...
you would seem to only require table2 to actually get and display the data.

if you want to hide the subsequent name values then that normally considered
to be a client application responsibility...
SQL is primarily concerned with manipluating the sets of data in your tables,
and isn't really bothered about how the results should be presented...

Avatar of mastoo
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have done in the end. you need to use multiple cursors to have such an output. The second cursor will be based on a WHERE condition with a variable from the first cursor...and the you print it