MySql Select multiple columnsfrom distinct rows based on one column

235 pts.
Tags:
iSeries V6R1
MySQL
SQL statements
V6R1
I am building one sql query based on 5 select statements unioned all together. Each select is built as a variable with where clauses based on filters selected by the users. All 5 selects are concatenated together to build the one dynamic query. The one select below needs to be changed to select rows only for distinct lsornm values.

SELECT LSORNM as ord, LSSUFX as sfx, LSCUST as cst,            DATE (SUBSTRING(CHAR(LSDTOR),1,4) ||'-'|| SUBSTRING(CHAR       (LSDTOR),5,2) ||'-'|| SUBSTRING(CHAR(LSDTOR),7,2)) as dte,     LSITEM as pon, LSDESC as job, LSUNPR as amt, 'DEL' as sts, IFNULL(OPSOTYP,' ') as typ, IFNULL(MMAL01,' ') as nm1,    IFNULL(MMAL02,' ') as nm2 FROM SO05 left outer join OP101L2    on SO05.LSORNM = OP101L2.INVN101 AND SO05.LSSUFX = OP101L2.SUVX101 left outer join MM01 on SO05.LSCUST = MM01.MMNMBR WHERE ((MMCPRE = 'C') or (MMCPRE = 'S')) AND (LSCUST > ' ') AND ((LSDTOR >= 19980101) AND (LSDTOR <= 20991231))         

From what I have read, I think maybe group by or a sub query is the way to go, but cannot get it to work.  Any help would be appreciated.   



Software/Hardware used:
IBM iSeries AS400 V6R1

Answer Wiki

Thanks. We'll let you know when a new response is added.

<pre>
ORDER SUFFIX ITEM DESCRIPTION CUSTOMER UNIT ORDER
NUMBER NUMBER NUMBER PRICE DATE
lsornm lssufx lsitem lsdesc lscust lsunpr lsdtor
342372 0 WP0114517102265 GALV MI 150 TEE 001503 .9440 20110303
342372 0 WP0164000102585 3/4X2 GALV S40 STL NIP 001503 3.666565 20110303
342372 0 037721400 CMS-33 3/4 MXC C-STL UNION 001503 3.101600 20110303
342372 0 041500534 603 3/4 CXFIP ADPT 001503 2.537313 20110301
342372 0 041502766 611 3/4 CCC TEE 001503 1.900000 20110301
342372 0 0782105404 S-453 3/4 CXC SWING CHECK 001503 18.713200 20110301
342372 0 0782107144 T-521 3/4 MIP X HS BLR DRN VLV 001503 3.500000 20110301
342372 0 261040645 40645 3/4PEX X 3/4 FEM SWT AD 001503 1.086419 20110301
</pre>

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    ...to select rows only for distinct lsornm values. If you want distinct values for LSORNM, you have to decide how to handle each of the other columns in the SELECT list. If there are multiple values of LSSUFX, LSCUST or DATE for a given value of LSORNM, you need to put a specification in the statement to tell it which one of the multiple values to use. That must be done for each column that might return multiple values. Please provide examples of a few rows to give us some context. Also, if you can, describe a little more about what you mean by "distinct" for that column. Tom
    125,585 pointsBadges:
    report
  • Canuhp
    This file is deleted lines from sales orders. Let's say there is a sales order 12345 that has 15 lines of items originally ordered. Line 3, 12, and 15 were deleted from the original order, so there will be 3 records on this SO05 file each with the same lsornm of 12345. I only need to pick up the fact that this sales order is on that file, not that it is on there 3 times. The other fields it picks and whether they should be the same for all three deleted lines are: lsornm(order number - same), lssufx(order suffix - same), lscust(customer number - same), lsdtor(order date - vary), lsitem(item number - vary), lsdesc(item description - vary), lsunpr(unit price - vary). Here are some example of fields I would use from these records: Field name lsornm 152895 152895 152895 lssufx 00 00 00 lscust 000095 000095 000095 lsdtor 20100118 20100103 20100118 lsitem 12433910S 124396797 1244L3175 lsdesc Liquid disp handle kit shower valve lsunpr 31.452000 8.691500 78.451000
    235 pointsBadges:
    report
  • Canuhp
    Just saw that this didn't line up very well, so here they are comma separated: Here are some example of fields I would use from these records: Field name lsornm - 152895, 152895, 152895 lssufx - 00, 00, 00 lscust - 000095, 000095, 000095 lsdtor - 20100118, 20100103, 20100118 lsitem - 12433910S, 124396797, 1244L3175 lsdesc - Liquid disp, handle kit, shower valve lsunpr - 31.452000, 8.691500, 78.451000
    235 pointsBadges:
    report
  • DoneThat
    I believe you just need to Group By. Example... This SQL retrieves every record for Cust 101:
    /*  Every record output.  */
    SELECT	OD.od_custno,  OD.od_loadmo, OD.od_loadda, (OD.od_loadcn * 100) + OD.od_loadyr, OD.od_ordrno
    FROM	rap002001.orddtl AS OD 
    WHERE	OD.od_custno  =  101 
    WITH	NC;
    
    Custno Date OrdrNo 101 3 25 2011 1 101 3 25 2011 1 etc.... 101 3 28 2011 1 101 3 28 2011 1 etc.... This SQL outputs 1 record for Cust 101/Date/OrdrNo:
    /*  One record output.  */
    SELECT	OD.od_custno,  OD.od_loadmo, OD.od_loadda, (OD.od_loadcn * 100) + OD.od_loadyr, OD.od_ordrno
    FROM	rap002001.orddtl AS OD 
    WHERE	OD.od_custno  =  101 
    GROUP BY	OD.od_custno,  OD.od_loadmo, OD.od_loadda, (OD.od_loadcn * 100) + OD.od_loadyr, OD.od_ordrno
    WITH	NC;
    
    Custno Date OrdrNo 101 3 25 2011 1 101 3 28 2011 1 Group By all of the fields in your select. As long as all fields are the same value within the order you will get 1 record per order number. Gary
    830 pointsBadges:
    report
  • Canuhp
    That's part of the problem, the value of all the fields is not the same within the records for the same sales order.
    235 pointsBadges:
    report
  • carlosdl
    Canuhp, it would be easier if you post your example data in a tabular form (using the editor's "code" tool). Something like this:
    lsornm 	lssufx	lscust 	lsdtor 		lsitem 		lsdesc 		lsunpr 	
    
    152895	00	000095	20100118	12433910S	Liquid disp	31.452000	
    152895	00	000095	20100103	124396797	handle kit	8.691500
    ...
    Also, I would recommend posting and example of the results you would want to get from the example data you posted, so we don't have to guess. I'm pretty sure you will get the solution to your requirement if you provide enough details.
    69,225 pointsBadges:
    report
  • TomLiotta
    Also, note that a GROUP BY doesn't help with the "distinct values for LSORNM". There can be any number of groups that would have the same value for LSORNM. A GROUP BY will generate distinct values for the composite of the GROUP BY columns, but not for just one of a set of GROUP BY columns. For the example where a distinct value of LSORNM is 152895, there are two possible values for LSDTOR -- 20100118 and 20100103. If you will have only a single row with LSORNM = 152895, and you want to have LSDTOR in the same row, there must be a rule that specifies which value to use. However, it's not clear if that's what you really want. I only need to pick up the fact that this sales order is on that file, not that it is on there 3 times. Then what use are the other fields? Why SELECT them at all? If they're needed in the final result set, there's essentially no way to have DISTINCT LSORNM values (in the result set). (And that's not technically true because recursive SQL could be used, but that doesn't seem to lead where you want to go. So, are you really only looking for a list of DISTINCT LSORNM values or will you be using the other columns from your original SELECT column list? You can't have both from a single SELECT (disregarding recursive SQL)? Tom
    125,585 pointsBadges:
    report
  • Canuhp
        lsornm       lssufx       lsitem        lsdesc              lscust      lsunpr       lsdtor
         152895        0         110009321    Sandstone sink        001503    201.010000    20101118
         152895        0         12433910S    Liquid dispenser      001503     31.452000    20101118
         152895        0         124274998    Tray Faucet           001503     25.264200    20101130
         152895        0         12447430W    Sink Faucet           001503     86.931700    20101130
    
    235 pointsBadges:
    report
  • Canuhp
    In the data I showed above, I would absolutely have to have the lsornm, lssufx, lscust, the most recent lsdtor, and (this is a new requirement) the sum of the dollars for all records for this sales order. The other fields I have talked about are needed for the results record, but I can force them to be a blank or space on these records if needed. So, for the above data, the result set would need to look like:
        lsornm       lssufx      lscust     lsdtor
         152895        0         001503    20101130 
    
    235 pointsBadges:
    report
  • Canuhp
    Oops, forgot the sum. Assuming quantities on each of these records is 1, i would also need to return the sum value of 344.65.
    235 pointsBadges:
    report
  • carlosdl
    Based on the results you posted, something like this should suffice:
    SELECT lsornm,lssufx,lscust,MAX(lsdtor),SUM(lsunpr)
    FROM your_table
    GROUP BY lsornm,lssufx,lscust;
    69,225 pointsBadges:
    report
  • TomLiotta
    I agree with Carlosdl. The MAX() and SUM() functions allow the GROUP BY to provide single values for those columns. The actual GROUP BY columns should all work as effectively 'distinct' as long as LSSUFX doesn't add a complication. (Customer number should work out fine.) Any remaining columns that you need might still need attention, but we'll have to know how they should affect results. Tom
    125,585 pointsBadges:
    report
  • Canuhp
    With your advice and forcing literals into the columns that I don't absolutely need, I think this is going to work. Preliminary testing looks good. Thanks for all the help!!
    235 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following