sorting records by minimum of the same value 1

[karweng]

Hi everyone,

I have been asked to help generate a report sort the records by the minimum Days value of the same catalogue from table A to resultA, any advise on how to solve this?

Table.A
Catalogue Material Days
45478-2 INLAY BACK 1781
45478-2 INLAY FRONT 1781
SIC-0336 INLAY BACK 1777
SIC-0336 INLAY FRONT 1788
SIC-0365 INLAY FRONT 1794

result A
Catalogue Material Days
SIC-0336 INLAY BACK 1777
SIC-0336 INLAY FRONT 1788
45478-2 INLAY BACK 1781
45478-2 INLAY FRONT 1781
SIC-0365 INLAY FRONT 1794

 

[MisterMo]

Database and CR Version always helpful

Here is what you can do from a SQL point of view

SELECT DISTINCT T1.Catalogue, T1.Material, T1.Days AS Days, MIN(T2.Days) AS Expr2
FROM dbo.Table T1 INNER JOIN
dbo.Table T2 ON T1.Catalogue = T2.Catalogue
GROUP BY T1.Catalogue, T1.Material, T1.Days
ORDER BY MIN(T2.Days)

this will work for MS-SQL and then just add it as a command on your report depending on the version you have



Mo

 

[karweng]

Hi Mo,

I think I didnt explain myself well enough and confused everyone, what I would like is, from table.A records to be grouped by Catalogue and sorted by the minimum Days, ResultA is how I would like the report to display.

 

[MisterMo]

And this is exactly what you get if you run this query from query analyser.

By looking at your profile i guess you are using access which I don't have but the logic is the same.

I don't know which version of crystal you are using so I can only advise on the one I am using which is 10 and from there I can add a command instead of tables and simply paste the query as it is, add the fields to the rport and run it.


I have tested it and it works.


Mo

 

[lbass]

Another approach would be to group on {table.catalogue} and then right click on {table.days} and insert a minimum. Then go to report->group sort-> and choose "minimum of {table.catalogue}". This will sort the groups correctly. Then suppress the group header and footer, and go to report->sort records and add the days field to get the ascsending sort within groups.

-LB

 

[MisterMo]

which is the logic used on a query using the same table twice grouping by one field on first table and sorting by min or max field on second table.

but your solution is better and deserve a Star

I've got to start to use Crystal Reports more often!!!!!

p.s. are you buying drinks when you reach 4 figures?


Mo