-3

i have this table 

CREATE TABLE IF NOT EXISTS `goldprice` (
`price` double unsigned NOT NULL,
`days` smallint(5) unsigned NOT NULL,
`seconds` mediumint(5) unsigned NOT NULL,
`sid` smallint(4) unsigned NOT NULL,
`gid` smallint(4) NOT NULL,
PRIMARY KEY (`days`,`seconds`,`sid`),
KEY `sid` (`sid`),
KEY `gid` (`gid`),
KEY `days` (`days`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and i want minimum and maximum price of each day first and last price of each day (base on its seconds) with using subquery i can solve some part of my problem before grouping i make a subquery and sorting in it result in mysql is true min, max, and last or first (based on sort type) can be made two important things remains last and first both is required performance is very important subquery seems not good

and i have his sql

SELECT price, days, seconds FROM goldprice
where gid=1 and days>=16200 group by days
order by days desc, seconds desc

change "days>=16200" to "days=16200" will returns different result in "days=16200" row.

the sort is not remaining desc.

i know behavior of my sql group by

but i can't find good solution for my needs

MySQL order by before group by

Community
  • 1
  • 1
user3580595
  • 11
  • 6

2 Answers2

0

Your query is incorrect. You select days with a random price match and a random seconds match. You should decide what price and seconds you want to show per day. The SUM? The MINinum? The MAXimum?

When starting with GROUP BY, you should make sure that for each column you either group by it or aggregate it (e.g. use SUM(price) instead of price alone or have price in the group by clause).

Example:

select a, b, MIN(c), MAX(d), e
from mytable
group by a, b;

a and b are okay, because you group by them. MIN(c) and MAX(d) are okay, because you aggregate c and d. e is incorrect; it is neither in the group by clause nor being aggregated. This is allowed in MySQL, but it's an advanced feature one must be aware of and handle that carefully. Above select statement would give just any of the matching e per a, b - the minimun e, the maximum e or just any other e. Only do this when you know that e is unique for a, b or you don't care what e you get. As said, it's an advanced feature.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • This is what I am doing. Don't select price. Select SUM(price), MAX(price) or whatever price aggregation you want to see. Same for minutes. Only you know what you actually want to select. – Thorsten Kettner May 21 '14 at 06:14
0

my solve is here

select g2.days, group_concat(price) prices, 
group_concat(seconds) times, minp, maxp from goldprice g1
inner join (
SELECT gid, days, 
max(price) as maxp, min(price) as minp,
max(seconds) as maxt, min(seconds) as  mint
FROM `goldprice` where gid = 1 and days = 16200
group by days
) g2
on (g1.days = g2.days and g1.gid = g2.gid and (seconds = mint or seconds = maxt))
 where gid = 1  and days = 16200
group by days order by days desc, seconds asc

is my solve is correct

user3580595
  • 11
  • 6
  • Yes and no. To start with: you have two order by clauses which should even result in a syntax error. Then you use min and max as names. You must not do this; these are reserved words. You right outer join, although it is not possible for a day's min and max seconds not to exists (and then you use g1 values in the where clause, thus turning the outer join into an inner join anyway). You should order your group_concat strings. But yes, the query gives you the prices you want to see. – Thorsten Kettner May 23 '14 at 12:26
  • thanks for helping i edited my errors (syntax error and reversed keys). order in group_concat is done by order seconds asc and is eq with times code can control it for me. – user3580595 May 24 '14 at 12:48
  • No, the sorting may coincidantally work for you, but the order for the items in group_concat must be specified within group_concat: `group_concat(price order by seconds)`. The seconds on the other hand in your order by clause add nothing, because days order the list completely already. – Thorsten Kettner May 24 '14 at 15:52