Cannot get the desired Orderby

[dbadmin]

Hi SQL Gurus,

I have a table with the following data

Col1 Col2
1 Property01
2 Property03
3 Property02
1 Property05
1 Property11
1 Property12
1 Property06

I need an order by select statement which will give me an output like below

1 Property01
1 Property05
1 Property06
1 Property11
1 Property12
3 Property02
2 Property03

that is, if the first column has duplicates, it should be together, otherwise order by col2 Is it possible?

Really appreciate your answers.

Thanks,
dbadmin

 

[DJN]

Group on column 1, sort on colunm 2

 

[Golom]

There is this sort of thing

SELECT A.Col1, A.Col2
    , (Select IIF(Count(*)>1,1,0) 
       From tbl Where tbl.Col1 = A.Col1) As [Y]
FROM tbl A
Order By 3, 1, 2

 

[dbadmin]

Hi Golom,

Thanks for the reply. Is the IIF function available in Access97? I am using Access97.

Thanks,
dbadmin

 

[Golom]

I think so but don't recall. Long time since I used A'97.

The SQL needs to be a bit more elaborate than the above and even the following assumes that columns are text columns and each row has the same number of characters in the respective columns. If they don't then you will need to left-pad the values to make it work properly.

Select * From (
SELECT A.Col1, A.Col2
     , IIF((Select IIF(Count(*)>1,1,0) From tbl
           Where tbl.Col1 = a.Col1) = 1,
           "1" & A.Col1 & A.Col2, 
           "0" & A.Col2 & A.Col1) AS K
FROM tbl AS A) 
ORDER BY 3

 

[dbadmin]

Hi Golom,

Thanks! I will test it today.

dbadmin