1

Table has 3 columns in a table: ITEM, SUB_ITEM, DATE_CREATED.

ITEM - item id (string) SUB_ITEM - item id (int) DATE_CREATE - date the SUB_ITEM was created (date)

Scenario:

  1. There are 3 Different item's (AAA1, AAB2, ABB3)
  2. All 3 of these item's have multiple sub-items.
  3. Each item has a sub-item that is the same for each of them (eg. All 3 of the item's have a SUB_ITEM = 101010)

I am trying to do something like:

select * from table group by ITEM, SUB_ITEM, DATE_CREATED

How do you make it display only 1 row? I don't care if it chooses AAA1 or AAB2 or ABB3, I just want it to pick 1 and remove the rest so it will show 1 row per SUB_ITEM, but still displays at least one of the parent items.

Edit:

Thank you to mathguy for answering the above question.

Question 2: Is it possible to group by the 1st 2 letters of the item in addition to the sub_item? So instead of returning 1 row, return 2 rows: AAA1 and AAB2 will cascade in to 1 row, and ABB3 will be the 2nd row because 'AA' and 'AB' are different.

Edit 2: See Main Answer comments for answer to question 2

Improve this question
1
  • 2
    Can you provide some sample tabular data and expected output based on it? Commented Jan 19, 2017 at 17:00

1 Answer 1

Reset to default
2

One way is to group by sub_item, and take the max or min over another column (let's say max over date_created) and whatever is in the remaining column IN THE SAME ROW.

select min(item) keep (dense_rank last order by date_created) as item, sub_item, max(date_created) as date_created from table_name group by sub_item ;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I adapted this to my solution and answers my question perfectly. Is it possible to add something like grouping by the 1st 3 char's of the item? I have edited the question.
@AntonToshik - Yes, did you try? In the post you show grouping by the first two characters (not 3). Change the group by clause to group by sub_item, substr(item, 1, 2).
yes i did try that, it didn't work, hence i decided to ask a question. But if it's meant to work i might have missed something then, ill make sure to double check.
@AntonToshik - Aleksej posted an answer which he has deleted, but in it he had created some test data which I used. I tested adding substr(item, 1, 2) to group by and it works as expected. You said you adapted what I wrote to your situation - perhaps the adaptation causes it to work differently? (Also, did you try it with 2 characters, not 3?)
the length of the sub string isn't the problem, i just wanted the syntax for the described situation in the question, the real query I am working with is quite lengthy and hard to read. But if group by sub string should work i will try to find the mistake.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.