SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View Help


View Help

Author
Message
asm1212
asm1212
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 432
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23709 Visits: 9730
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
asm1212
asm1212
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 432
Yes I have but just adding MAX to invoice id column does not work...
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39734 Visits: 38563
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
asm1212
asm1212
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 432
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
asm1212
asm1212
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 432
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...
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5489 Visits: 35451
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

Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26293 Visits: 17553
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
asm1212
asm1212
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 432
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!
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26293 Visits: 17553
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search