Group by feature messing up order by date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by feature messing up order by date

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-08-11 : 08:59:18

SELECT tblResidential_Sales.id, tblResidential_Sales.address
FROM tblResidential_Viewings INNER JOIN
tblResidential_Sales ON tblResidential_Viewings.propertyId = tblResidential_Sales.id
WHERE (tblResidential_Sales.id IN
(SELECT TOP (100) PERCENT tblResidential_Viewings_1.propertyId
FROM tblResidential_Viewings AS tblResidential_Viewings_1 INNER JOIN
tblResidential_Sales AS tblResidential_Sales_1 ON tblResidential_Viewings_1.propertyId = tblResidential_Sales_1.id
WHERE (CONVERT(INT, tblResidential_Viewings.date, 103) >= CONVERT(INT, GETDATE() - 1, 103)) AND (tblResidential_Sales_1.workflowStatusId = 3) AND (tblResidential_Sales_1.saleTypeId = 1)
ORDER BY tblResidential_Viewings_1.date))
GROUP BY tblResidential_Sales.id, tblResidential_Sales.address

The idea is to have all items sorted by tblResidential_Viewings.date but they arent, they seem to be sorted be Id instead

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 09:04:05

try


SELECT tblResidential_Sales.id, tblResidential_Sales.address
FROM tblResidential_Viewings INNER JOIN
tblResidential_Sales ON tblResidential_Viewings.propertyId = tblResidential_Sales.id
WHERE (tblResidential_Sales.id IN
(
	SELECT PERCENT tblResidential_Viewings_1.propertyId
	FROM tblResidential_Viewings AS tblResidential_Viewings_1 INNER JOIN
	tblResidential_Sales AS tblResidential_Sales_1 ON tblResidential_Viewings_1.propertyId = tblResidential_Sales_1.id
	WHERE tblResidential_Viewings.date>=dateadd(day,datediff(day,0,getdate()),0) and
	 AND tblResidential_Viewings.date<dateadd(day,datediff(day,0,getdate()),1) (tblResidential_Sales_1.workflowStatusId = 3) AND (tblResidential_Sales_1.saleTypeId = 1))
)
GROUP BY tblResidential_Sales.id, tblResidential_Sales.address
ORDER BY tblResidential_Viewings.date

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 09:11:02

[code]SELECT rs.ID,
rs.Address
FROM tblResidential_Viewings AS rv
INNER JOIN tblResidential_Sales AS rs ON rs.ID = rv.PropertyID
WHERE rv.Date >= DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0)
AND rs.WorkflowStatusID = 3
AND rs.SaleTypeID = 1
GROUP BY rs.ID,
rs.Address
ORDER BY MIN(rv.Date)[/code]

E 12°55'05.25"
N 56°04'39.16"

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-08-11 : 10:22:56

SELECT rs.ID,
rs.Address
FROM tblResidential_Viewings AS rv
INNER JOIN tblResidential_Sales AS rs ON rs.ID = rv.PropertyID
WHERE rv.Date >= DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0)
AND rs.WorkflowStatusID = 3
AND rs.SaleTypeID = 1
GROUP BY rs.ID,
rs.Address
ORDER BY MIN(rv.Date)

This worked brilliant for me, the only thing i need to change is I need a secondary ordering
i.e MIN(rv.Date) , rv.Price how would I get around this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:26:38

[code]GROUP BY rs.ID,
rs.Address,
rv.Price
ORDER BY MIN(rv.Date),
MIN(rv.Price)[/code]

E 12°55'05.25"
N 56°04'39.16"

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-08-11 : 10:29:46

would it be Max(Price) to display the highest price first ye ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:31:40

We can't tell.
You know your business rules.

But since MAX (like MIN) is an aggregated function, it doesn't matter for the query.
The syntax is the same, the result will be the same.

The only thing that changes is the sort order of the resultset.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:32:10

[code]GROUP BY rs.ID,
rs.Address,
rv.Price
ORDER BY MIN(rv.Date),
MAX(rv.Price) DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources