August 6, 2012 at 1:31 pm
Hey guys,
So I have this view, and in creating this view, I want to pull all these invoices that were created in the past month...However if I pull back 2 invoices that have different invoice ids but they have the same group id, same subscriber id, and same due date, I just need to pull the max invoice id...How can I do that?
Thanks
August 6, 2012 at 1:33 pm
Are you familiar with aggregate functions (like MAX and MIN) in queries? You can use those in Views, just like any other query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 2:17 pm
Yes I have but just adding MAX to invoice id column does not work...
August 6, 2012 at 2:21 pm
Unfortunately, we can't see what you see, nor do we know what the final result should look like. Just not enough information to give you a solid answer.
August 6, 2012 at 2:27 pm
Here is a sample result set:
InvoiceID DueDate GroupID SubscriberID
101 1/31/12 4 22222
102 1/31/12 4 22222
103 1/15/12 5 33333
So you see the first 2 records have the same due date, same group id , and same subscriber Id and the 3rd record is completely different...
I want my final result set to be:
InvoiceID DueDate GroupID SubscriberID
102 1/31/12 4 22222
103 1/15/12 5 33333
August 6, 2012 at 2:29 pm
I apologize, I forgot the formatting does not reflect how it was typed in the message...
I remember now that you have to create custom code to reflect what I am asking and it makes it easier for yall to understand ... Unfortunately, I am on a time constraint and dont have time to go through all that so I apologize and will just try to keep working with what i have and see if i can come up with what I am needing...
August 6, 2012 at 2:46 pm
asm1212 (8/6/2012)
I apologize, I forgot the formatting does not reflect how it was typed in the message...I remember now that you have to create custom code to reflect what I am asking and it makes it easier for yall to understand ... Unfortunately, I am on a time constraint and dont have time to go through all that so I apologize and will just try to keep working with what i have and see if i can come up with what I am needing...
try this...btw I have the same constraints
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst_InvoiceHeader]') AND type in (N'U'))
DROP TABLE [dbo].[tst_InvoiceHeader]
GO
CREATE TABLE [dbo].[tst_InvoiceHeader](
[invoice_id] [int] NULL,
[grouo_id] [int] NULL,
[subscriber_id] [int] NULL,
[duedate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tst_InvoiceHeader]([invoice_id], [grouo_id], [subscriber_id], [duedate])
SELECT 101, 4, 22222, '20120131 00:00:00.000' UNION ALL
SELECT 102, 4, 22222, '20120131 00:00:00.000' UNION ALL
SELECT 103, 5, 33333, '20120115 00:00:00.000'
select * from tst_InvoiceHeader
SELECT MAX(invoice_id) AS MaxId
FROM tst_InvoiceHeader
GROUP BY grouo_id, subscriber_id, duedate
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 6, 2012 at 2:53 pm
asm1212 (8/6/2012)
Unfortunately, I am on a time constraint and dont have time to go through all that so I apologize and will just try to keep working with what i have and see if i can come up with what I am needing...
No offense but seriously??? You don't have time to put together enough details to ask your question. Putting together the following sample data took me approximately 2 minutes.
create table MyInvoice
(
InvoiceID int,
DueDate date,
GroupID int,
SubscriberID int
)
insert MyInvoice
select 101, '1/31/2012', 4, 22222 union all
select 102, '1/31/2012', 4, 22222 union all
select 103, '1/15/2012', 5, 33333
select * from MyInvoice
You did a fine job explaining that you want a view and what it should look like. Using the MAX function as suggested produces exactly the results you stated from your sample data.
create view MyInvoiceViewWithMax
as
select MAX(InvoiceID) as InvoiceID, DueDate, GroupID, SubscriberID
from MyInvoice
group by DueDate, GroupID, SubscriberID
go
select * from MyInvoiceViewWithMax
Total time working on the total solution (about 5 minutes including posting the code).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2012 at 3:14 pm
Yeah no offense but my view is much larger than that, and i was trying to dumb it down...
I have tried using the MAX function, but it does not work when i try it...Im selecting about 50+ more columns than what I specified and therefore you have to group by all 50+ columns...so no I dont have the time to try to write up a sample view of what I am trying to accomplish...Man this site used to be a great resource but the last couple of times I have visited, it has become more of nuisance and waste of time...
Thank you all for your time and cooperation!
August 6, 2012 at 3:21 pm
asm1212 (8/6/2012)
Yeah no offense but my view is much larger than that, and i was trying to dumb it down...I have tried using the MAX function, but it does not work when i try it...Im selecting about 50+ more columns than what I specified and therefore you have to group by all 50+ columns...so no I dont have the time to try to write up a sample view of what I am trying to accomplish...Man this site used to be a great resource but the last couple of times I have visited, it has become more of nuisance and waste of time...
Thank you all for your time and cooperation!
I apologize if my response sounded a bit snarky. I totally get what you mean that you had to simplify it. And of course once you group by a zillion columns it may no longer return 1 row.
By all means don't write this place off as a solid resource. Those of us that post sometimes get bogged down by the lazy people who just want their answer right now because they are under a time crunch and as a result we can sometimes sound a bit snarkish. Sadly the quality of the help is direct result of the quality of the post. Given that you basically said to ignore it I probably should have. Instead I chose to give you an answer to your simple question and berated you at the same time. My apologies.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2012 at 3:23 pm
asm1212 (8/6/2012)
Yeah no offense but my view is much larger than that, and i was trying to dumb it down...
Which is why you got a simplified answer.
I have tried using the MAX function, but it does not work when i try it...Im selecting about 50+ more columns than what I specified and therefore you have to group by all 50+ columns...so no I dont have the time to try to write up a sample view of what I am trying to accomplish...Man this site used to be a great resource but the last couple of times I have visited, it has become more of nuisance and waste of time...
You asked a generic question, and got a generic answer. This is more specific that you want to avoid group-bys. What did you expect? *confused*
You want to use a correllated subquery in that case, but be aware that you can end up with a significant performance hit if you try to use it as your where clause from outside the view because it needs to compute the entire view before it can be restricted. These can kill optimization completely, so it's a use at your own risk. The distinct is there because otherwise you'll repeat the MAX(id) for each id that repeats all the other information, so you end up grouping anyway but you avoid the massive column repeat you'll need to do with the GROUP BY clause.
Basically:
SELECT DISTINCT
(SELECT MAX( InvoiceID) FROM tbla t2 WHERE t2.field1 = t.field1 AND t2.Field2 = t.field2) AS InvoiceID,
... rest of columns
FROM
tbla t
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 6, 2012 at 3:44 pm
asm1212 (8/6/2012)
...Im selecting about 50+ more columns than what I specified and therefore you have to group by all 50+ columns...
no offence...but 50 levels of grouping seems a lot...are you sure that you cannot exclude some of these columns....is data in some of these columns being repeated?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 6, 2012 at 4:01 pm
I think what it comes down to is providing enough information to understand the problem so that a good solution can be provided.
What you posted just didn't provide that information. With 50 columns of data, grouping on all those columns may not provide you with the answer you are looking for. But how are we supposed to know what you are trying to accomplish if you don't tell us?
I am sorry, but I have a hard time being sympathetic when you get upset that we can't answer your question when we don't know enough to give you an answer.
You say you want the max invoice number if they have the same group_id, subscriber_id, and duedate. What about all the other columns, where will they come from in the view?
You could start the view with a simple CTE:
WITH Invoices_CTE as (
SELECT
MAX(invoice_id) AS MaxId
FROM
InvoiceHeader
GROUP BY
group_id,
subscriber_id,
duedate)
This gives you a list of Invoice Numbers (invoice_id's) that you would then join the other information.
Hope this gives you an idea of where to go from here.
August 7, 2012 at 5:59 am
asm1212 (8/6/2012)
Yeah no offense but my view is much larger than that, and i was trying to dumb it down...I have tried using the MAX function, but it does not work when i try it...Im selecting about 50+ more columns than what I specified and therefore you have to group by all 50+ columns...so no I dont have the time to try to write up a sample view of what I am trying to accomplish...Man this site used to be a great resource but the last couple of times I have visited, it has become more of nuisance and waste of time...
Thank you all for your time and cooperation!
Are you familiar with CTEs? Are you familiar with the ranking functions in T-SQL?
At the top of the view, write a CTE that queries all the columns you want to query, plus a column using Row_Number(). Partition that column by the columns you want to "group by", order it by the invoice ID descending. In your outer query, incude a Where clause that filters by that column = 1. You'll get what you need.
P.S.: Getting insulting towards people who are trying to help you isn't very polite.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 7:09 am
Guys,
This has gotten overblown...
When I first posted that sample code stuff and realized it wasnt formatted once I finished posting, I remembered that there was a specific way yall like for people to post to help yall understand better what is being asked...And I said I apologize for posting the sample stuff in that way and that I just didnt have the time to post my whole process b/c I knew it was much larger than I originally had...And I said I would go on, then someone made a post that kind of rubbed me the wrong way and then I responded with my little rant which I know was not the best thing for me to do, but I have been fighting this for awhile and when I read that post, it frustrated me! I do know yall here to help and I understand why yall want folks to post in a certain manner...I just want to be clear that was not my reason for my previous post!
Thank you all who responded with ideas how to get this accomplished...I was looking at how I can use the OVER PARTITION BY clause...
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply