I cant do a Min() Group By?

I can't beleive it i'm supose to be a DB programmer and i can't think how to do this min() group by:

I need the return the SupID only, for each of the cheapest 'QFProductRef'
Ie the result should be (1,6,3,4)

table is called supplierInventory
SupIDQFProductRefDLRPriceAfterDisc
160138355.41
2601139416.12
3601387411.03
4600885312.25
5601383512.09
660113947.11
7601387412.81
8600885314.22

I'll have to go back to being a lumber jack if someone help!.
thanks in advancecan you xplain a little more...|||This query returns 4,6,1,3. If there were two or more records that tie for the lowest price then it would return each of their SupIDs.

Select supplierInventory.SupID
 FROM
 (Select QFProductRef, Min(DLRPriceAfterDisc ) as DLRPriceAfterDisc
 From supplierInventory
 Group By QFProductRef
 ) A
 INNER JOIN supplierInventory ON A.QFProductRef=supplierInventory.QFProductRef AND A.DLRPriceAfterDisc=supplierInventory.DLRPriceAfterDisc

I Cant "show sql query" - Crystal Report 8.5

Hey Guys, all fine ?

I created a report and using 3 tables( or more) with joins, group and etc. And this report calls a subreport. Well, the report is perfect, no problems! But, i can't see your sql definition(In crystal report, menu database,Show sql query) and, in Visual Basic i need to set a similar sql of report, only changing the where clause, by filter controls in VB.

In old times, i have used crystal report 8, and can show sql query.

In VB, how i can input a sql string in report, after execute it ?

How i can show sql query in CR?

Please, i need this help with urgency! The rope is in my neck! =(]

Thanks a lot!"Show SQL Query" option is very much present in crystal reports 8.5, if you don't have the option, then you try re-installing crystal reports.

I bet this is simple, -But see if you can help. Hard to come up with Title -Have a lo

Hi,
What I would like to be able to do in simple SQL is to group on a term
that has come from a longer string. As below.
I begin with data in this format.
PARTNO DESC
12345-01 Capacitor C1 requires resoldering
12345-01 Capcitor broken, replace C1
12345-01 Solder Quality IC U1
12345-01 U1 Misaligned
25698-05 R115 Cracked
25698-05 Dry solder joing R115
And would like it to appear like this.
PARTNO DESC Count
12345-01 C1 2
12345-01 U1 2
25698-05 R115 2
If you could help that would be awesome. I also have to find the faults
in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
Many Thanksphilipbennett25 wrote:
> Hi,
> What I would like to be able to do in simple SQL is to group on a term
> that has come from a longer string. As below.
> I begin with data in this format.
> PARTNO DESC
> 12345-01 Capacitor C1 requires resoldering
> 12345-01 Capcitor broken, replace C1
> 12345-01 Solder Quality IC U1
> 12345-01 U1 Misaligned
> 25698-05 R115 Cracked
> 25698-05 Dry solder joing R115
> And would like it to appear like this.
> PARTNO DESC Count
> 12345-01 C1 2
> 12345-01 U1 2
> 25698-05 R115 2
>
> If you could help that would be awesome. I also have to find the faults
> in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
> Many Thanks
It would help if yuo could create a table of the descriptsion first:
CREATE TABLE part_descriptions (descr VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO part_descriptions (descr)
SELECT 'C1' UNION ALL
SELECT 'U1' UNION ALL
SELECT 'R115' ;
Then try this:
SELECT T.partno, P.descr, COUNT(*) AS cnt
FROM tbl AS T
JOIN part_descriptions AS P
ON T.descr LIKE '%'+P.descr+'%'
WHERE P.descr LIKE '%'
GROUP BY T.partno, P.descr ;
You could also make use of PATINDEX:
DECLARE @.p VARCHAR(20)
SET @.p = 'C1'
SELECT partno, descr, COUNT(*) AS cnt
FROM
(SELECT partno, SUBSTRING(descr,PATINDEX('%'+@.p+'%',desc
r),LEN(@.p))
FROM tbl
WHERE descr LIKE '%'+@.p+'%') AS T(partno,descr)
GROUP BY partno, descr ;
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL,
col VARCHAR(50) NOT NULL
)
INSERT INTO #Test VALUES ('20050101','Hello world C1')
INSERT INTO #Test VALUES ('20050101','Hello C1 world ')
INSERT INTO #Test VALUES ('20050201','M favorite is D1')
INSERT INTO #Test VALUES ('20050201','D1 Is my Favorite ')
SELECT dt,descr,COUNT(*)
FROM
(
SELECT dt,CASE WHEN col LIKE '%c1%' THEN 'c1'
ELSE CASE WHEN col LIKE '%d1%'THEN 'd1' END END descr
FROM #Test
) AS D GROUP BY dt,descr
"philipbennett25" <pbennett@.xyratex.com> wrote in message
news:1133779185.731612.3100@.g49g2000cwa.googlegroups.com...
> Hi,
> What I would like to be able to do in simple SQL is to group on a term
> that has come from a longer string. As below.
> I begin with data in this format.
> PARTNO DESC
> 12345-01 Capacitor C1 requires resoldering
> 12345-01 Capcitor broken, replace C1
> 12345-01 Solder Quality IC U1
> 12345-01 U1 Misaligned
> 25698-05 R115 Cracked
> 25698-05 Dry solder joing R115
> And would like it to appear like this.
> PARTNO DESC Count
> 12345-01 C1 2
> 12345-01 U1 2
> 25698-05 R115 2
>
> If you could help that would be awesome. I also have to find the faults
> in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
> Many Thanks
>|||Hey guys, thanks for your help on this. The Database I am querying is
not a SQL Server therefore it has to be done using basic SQL. It also
needs to be able to grow and shrink depending on what it Finds in the
DESC Field. I will be searching for the keywords in the DESC column by
looking for patterns, not having specific keywords already defined. IE
I would just have "Like 'C_' ".
Many Thanks