Having trouble with GROUP BY ...

[Daniel Lee]

SELECT TOP 25 *
FROM new_ticket
WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC)
ORDER BY group_num DESC


I am using the above method to get pagination with PHP Script.
It is running on MS SQL Server 2005 Express Edition.

After long been trying, I haven't found a way to get them grouped.
I modified very last line "GROUP BY group_num" and didn't work!

Is anyway this can be grouped and arrayed DESC order?
I need someone very Smart and save me!!

[ck9663]

SELECT TOP 25 *
FROM new_ticket
WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC)
ORDER BY group_num DESC


I am using the above method to get pagination with PHP Script.
It is running on MS SQL Server 2005 Express Edition.

After long been trying, I haven't found a way to get them grouped.
I modified very last line "GROUP BY group_num" and didn't work!

Is anyway this can be grouped and arrayed DESC order?
I need someone very Smart and save me!!

am not a very smart but i can try to help you...

SELECT TOP 25 group_num
FROM new_ticket 
WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC) 
GROUP BY group_num 
ORDER BY group_num
 

that is, if group_num is all you need. as a rule of thumb, you have to include in GROUP BY all non-aggreate functions in your select list

[Daniel Lee]

Thanks for your help. But, that's not exactly what I want.
There are more columns other than group_num and I'd like to display all other colunms based on the group_num displayed.

What I want to see is that other columns lined up according to the group numbers resulted from your SQL query.

Thanks

am not a very smart but i can try to help you...

SELECT TOP 25 group_num
FROM new_ticket 
WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC) 
GROUP BY group_num 
ORDER BY group_num
 

that is, if group_num is all you need. as a rule of thumb, you have to include in GROUP BY all non-aggreate functions in your select list

[ck9663]

Thanks for your help. But, that's not exactly what I want.
There are more columns other than group_num and I'd like to display all other colunms based on the group_num displayed.

What I want to see is that other columns lined up according to the group numbers resulted from your SQL query.

Thanks

if you don't have duplicate group_num then this would be it

      SELECT TOP 25 *
      FROM new_ticket
      WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC)
      ORDER BY group_num

[Daniel Lee]

Your worst guess was right. There are duplicate group_nums
I tried JOIN but didn't work.

id , group_num , issue
1 , 1 , A
------------------------------------------------------
2 , 1 , B
------------------------------------------------------
3 , 2 , C
------------------------------------------------------
4 , 2 , D
------------------------------------------------------



I want to see somthing like this.
id , group_num , issue
-------------------------------------
1 , 1 , A
--------------------------------------
3 , 2 , C
-------------------------------------

if you don't have duplicate group_num then this would be it

      SELECT TOP 25 *
      FROM new_ticket
      WHERE group_num NOT IN (SELECT TOP 75 no FROM new_ticket ORDER BY no DESC)
      ORDER BY group_num

[ck9663]

Your worst guess was right. There are duplicate group_nums
I tried JOIN but didn't work.

id , group_num , issue
1 , 1 , A
------------------------------------------------------
2 , 1 , B
------------------------------------------------------
3 , 2 , C
------------------------------------------------------
4 , 2 , D
------------------------------------------------------



I want to see somthing like this.
id , group_num , issue
-------------------------------------
1 , 1 , A
--------------------------------------
3 , 2 , C
-------------------------------------

what's the factor to consider 1,1,A instead of 1,1,B ? that would affect your query. if this is a troubleshooting ticket table, are you planning to get the first record or the latest one?

[Daniel Lee]

I am migrating MYSQL to MS-SQL. MYSQL script works fine
when use * with GROUP BY group_num and it shows all fileds in each row.
but Mssql doesn't support SELECT * and GROUP BY one_column.

id , group_num , issue, customer_name
1 , [1] , Wiper , john
------------------------------------------------------
2 , [3], Transmission , bill
------------------------------------------------------
3 , [3] , Engine , mike
------------------------------------------------------
4 , [3] , Engine , david
------------------------------------------------------


I want to see somthing like this.
As long as group_num shows only unique number, that will eliminate my headaches. The 2nd row could be 2 ,[3] Engine, david. As long as only one group_num appears on the list page that will be okay.

1 , [1] , Wiper , john
------------------------------------------------------
2 , [3] , Transmission , bill
------------------------------------------------------

what's the factor to consider 1,1,A instead of 1,1,B ? that would affect your query. if this is a troubleshooting ticket table, are you planning to get the first record or the latest one?

[ck9663]

I am migrating MYSQL to MS-SQL. MYSQL script works fine
when use * with GROUP BY group_num and it shows all fileds in each row.
but Mssql doesn't support SELECT * and GROUP BY one_column.

id , group_num , issue, customer_name
1 , [1] , Wiper , john
------------------------------------------------------
2 , [3], Transmission , bill
------------------------------------------------------
3 , [3] , Engine , mike
------------------------------------------------------
4 , [3] , Engine , david
------------------------------------------------------


I want to see somthing like this.
As long as group_num shows only unique number, that will eliminate my headaches. The 2nd row could be 2 ,[3] Engine, david. As long as only one group_num appears on the list page that will be okay.

1 , [1] , Wiper , john
------------------------------------------------------
2 , [3] , Transmission , bill
------------------------------------------------------

how's this:

select * from new_ticket a inner join 
(select group_num, max(id) as id from new_ticket group by group_num) b on a.group_num = b.group_num and a.id = b.id
where <that condition you have to check for the top 75% or something>