0

Bottom Line Up Front: I am trying to find a way to format the output so that some data is left blank if it matches a preceding row.

I've edited as suggested by @philipxy because I really do want to learn how to write better code and write better questions to support that learning.

That being said, @ysth was able to solve my overly complex ask anyway. I changed the title and marked answered.

This is a sample table from my database: (SELECT codename, dt_begin, id_alias FROM aliases GROUP BY codename;)

+--------------+------------+----------+
| codename     | dt_begin   | id_alias |
+--------------+------------+----------+
| Arachniblade | 1999-12-23 |        1 |
| Arachniblade | 2016-07-04 |        2 |
| Beta         | 2015-06-03 |        1 |
| Beta         | 2016-07-04 |        3 |
| Cyberwolf    | 2016-07-04 |        1 |
+--------------+------------+----------+

I would like the second (and any subsequent) instances of 'Arachniblade' and 'Beta' to be blank when ORDER BY codename is used.

+--------------+------------+----------+
| codename     | dt_begin   | id_alias |
+--------------+------------+----------+
| Arachniblade | 1999-12-23 |        1 |
|              | 2016-07-04 |        2 |
| Beta         | 2015-06-03 |        1 |
|              | 2016-07-04 |        3 |
| Cyberwolf    | 2016-07-04 |        1 |
+--------------+------------+----------+

Similarly, if I ORDER BY id_alias I would like to see only id 1 printed once but still retain all three records for 'Arachniblade,' 'Beta,' and 'Cyberwolf.'

+--------------+------------+----------+
| codename     | dt_begin   | id_alias |
+--------------+------------+----------+
| Arachniblade | 1999-12-23 |        1 |
| Beta         | 2015-06-03 |          |
| Cyberwolf    | 2016-07-04 |          |
| Arachniblade | 2016-07-04 |        2 |
| Beta         | 2016-07-04 |        3 |
+--------------+------------+----------+

As @ysth mentioned LAG() is a part of the solution. I'm not sure how COALESCE fits in yet.

Wolfton
  • 13
  • 2
  • 1
    You must get the result as-is, and clear the same values on the client during the result printing. – Akina Dec 03 '20 at 04:36
  • Please use words to say how the output is a function of the input, don't expect us to guess from one example. Code that doesn't do what you want doesn't tell us what you want. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. – philipxy Dec 03 '20 at 04:38
  • 1
    Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Dec 03 '20 at 04:41
  • @philipxy I edited as you suggested in your second-most recent comment. Regarding the last comment, I reverted to the implicit inner join and yes, everything works as desired. I chose left because I was going to include some other tables and information later but that is not relevant to this ask. – Wolfton Dec 03 '20 at 07:53
  • 1
    If you're serious, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Dec 03 '20 at 08:33
  • There's still no MRE. My summary of what a MRE is begins, "cut & paste & runnable" etc. You don't give such code. Also you still have misleading "condition" `aliases.id_alias` which I said is the same as `aliases.id_alias<>0` & it's only correct--though redundant--if those ids are never 0, which you haven't confirmed. Assuming you address those (cut & paste and id) problems, a MRE includes a clear specification, per my 1st comment, also still missing. Then please also apply your clear specification to getting an appropriate query from this one in asking your question if you are still stuck. – philipxy Dec 03 '20 at 09:01
  • Replacing a value by blank/null if it is the same as the first value above that isn't blank/null is a faq. (Which one could expect.) Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. PS It's still not clear what pre-blankiing/nullifying output you want from input. – philipxy Dec 03 '20 at 09:05
  • If the "pre-blankiing/nullifying output you want" is the "The result I get is:" table then your question seems to be just the above faq. Googling with 'sql site:stackoverflow.com' & (cut & pasted from your post) 'blank if the preceding record is the same' gives me (with hits for doing the reverse) an answer from 2013 & maybe others. However there will be earlier hits for other phrasings. Also if you include 'mysql' you will get answers for pre-8.0 that don't use window functions. (But beware, pace SO answers, reading & writing a variable in 1 select statement is documented as undefined.) – philipxy Dec 03 '20 at 09:29
  • But if that's your question, you don't need to involve a join to ask it. – philipxy Dec 03 '20 at 09:41
  • @philipxy I see what you mean in that last comment. I'm editing the ask again and will remove the joins. Everything I am asking for can be done with one table - aliases. I have done as you said with the google search limiting to stackoverflow.com and I'm navigating all that to see how better to ask and also hopefully to find a solution that will work. – Wolfton Dec 04 '20 at 07:13

1 Answers1

0

So you don't want to GROUP BY anything, but you want Codename to be blank where it is equal to the previous row's Codename? You would select this instead of just aliases.codename:

IF(COALESCE(LAG(aliases.codename) OVER (),'')=aliases.codename,'',aliases.codename) AS 'Codename'

assuming mysql 8.0 or mariadb 10.2+. Full query:

SELECT IF(COALESCE(LAG(aliases.codename) OVER (),'')=aliases.codename,'',aliases.codename) AS 'Codename',
       aliases.dt_begin,
       public_ids.fname,
       public_ids.suffix
FROM   public_ids
JOIN   aliases ON aliases.id_alias 
WHERE  aliases.id_alias = public_ids.id
ORDER BY aliases.codename, aliases.dt_begin

(You omitted ORDER BY in your query; it wouldn't make much sense to want to do this without a specified order.)

On older versions, you can emulate LAG() with a variable:

SELECT IF(COALESCE(@lag,'')=aliases.codename,'',@lag:=aliases.codename) AS 'Codename',
       aliases.dt_begin,
       public_ids.fname,
       public_ids.suffix
FROM   (select @lag:=NULL) initvars
CROSS JOIN public_ids
JOIN   aliases ON aliases.id_alias 
WHERE  aliases.id_alias = public_ids.id
ORDER BY aliases.codename, aliases.dt_begin
ysth
  • 96,171
  • 6
  • 121
  • 214
  • thank you. That is exactly what I am trying to do. It does look like you fully understood me. I have been trying this since you posted it but I can not get it to work. It looks like I'm running version 5.7 in Goorm IDE. I've watched several videos on LEAD and LAG and I cannot reproduce their results. I have not yet found a good tutorial on COALESCE but if LAG isn't working for me COALESCE probably won't matter. I'm going to migrate to an Ubuntu VM that I created so that I can be sure that I'm using up to date software. – Wolfton Dec 04 '20 at 07:03
  • your LAG emulation worked perfectly! Thank you! – Wolfton Dec 04 '20 at 07:50
  • To take @ysth's answer and show where it would work with the new, simplified data set, I'll add it without my original joins and without the cross join (which I should have known that I needed) and multiple tables: ```SELECT IF(COALESCE(@lag,'')=codename,'',@lag:=codename), dt_begin, id_alias FROM aliases initvars ORDER BY codename, dt_begin;``` – Wolfton Dec 04 '20 at 17:59
  • Reading & assigning the same variable in the same select statement is undefined behaviour in MySQL, see the documentation re assignment & variables. – philipxy Dec 04 '20 at 20:37
  • @philipxy I think you are overstating the case; though if you'd like to provide a link that would be appreciated. Setting variables *at all* outside a SET statement could well be removed from some future version, but most of the reasons to use variables no longer apply with current versions – ysth Dec 04 '20 at 20:57
  • I just told you where it is documented. In 5.7 see 9.4 & 12.4.4. See [my comments here](https://stackoverflow.com/q/16715504/3404097) & [my answer here](https://stackoverflow.com/a/44751302/3404097) & their links. And assignment in select is deprecated as of 8.0. Note that the pre-8.0 documentation says some things about behaviour under read+write, but it's not clear & refers to undefined implementation notions, so justifies nothing. The documentation doesn't even clearly say what assignment use in select actually does. People think it's OK because others do--it's the blind leading the blind. – philipxy Dec 04 '20 at 21:18
  • @philipxy people not using modern versions often have to use variables to do basic things in sql. yes, everyone should upgrade. but I reject the notion that people who can't must do extra work client side. the documentation calls out two specific concerns: changing type, and order of evaluation for _expressions_ (e.g. `select @a, @a:=1`); neither concern applies here. – ysth Dec 04 '20 at 21:45
  • The documentation is exactly as I said it is. I'm done. – philipxy Dec 04 '20 at 21:50
  • shrug. people have been doing this for a long time; the documentation calls out a couple of gotchas that it's easy to trip on, but the behavior is pretty well understood. – ysth Dec 04 '20 at 22:08