|
I create some tables (the same ones mentioned below), but when i try to do select * from tablename (in a query window in SQL Manager Express), it tells me this:
Invalid object name 'tableName'.
I can see the table there, and I can modify it, but I can't run a query on it. I've even tried putting "dbo." at the beginning of the table name, but it still gives that error. Is it a database permissions thing? I haven't expressly set any permissions because I figured I didn't have to.
SOLUTION ==============
I guess I didn't have the database selected in the Object Explorer when I created the query window...
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
modified on Saturday, March 20, 2010 9:49 AM
|
|
|
|
|
you can set all kinds of user permissions, such as create table, create record, read table, etc. I don't know the details, Management Studio should offer a way to see them all. Myself, I mostly use MySQL and MyPHPAdmin.
FWIW: one way to get into trouble is by using a reserved word as a table name or field name; error messages aren't always very informative here.
|
|
|
|
|
None of my table names are reserved words... :/
Do I *need* to set permissions? Like I said, I can add/modify tables and stored procs, but I can't run a simple query in a query window.
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
John Simmons / outlaw programmer wrote: Do I *need* to set permissions?
Guess so. In PHPMyAdmin, when you create a database, you have to specify what permissions you give everyone, based on username and hostname (e.g. you can restrict a user to localhost only, no remote); there are over 20 checkboxes detailing all privileges.
Are you sure you are logged into your SQL Manager with the same username/password your app is running? (Not that you have to, but that should yield the same privileges).
|
|
|
|
|
Solution - See original post in this thread
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I have a table that has multiple instances of a given item, and the date on which that record was inserted into the table.
How do I retrieve the newest copy of all unique items in the table?
I'm an almost complete newbie regarding SQL, so please don't be harsh.
SOLUTION ===========
select field1, max(field2)
from table
group by field1
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Hi John,
try something like:
SELECT TOP 1 selectedFields FROM tablename WHERE matchConditions ORDER BY createdField DESC
|
|
|
|
|
I'd love to try that, but I can't even run a simple query (see next question above this thread).
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Here's my situation:
0) There are a number of records for a given "id", and there is more than one "id" in the table. So, I could have 10 records for id1, and 5 records for id2.
1) These records contain a datetime column on which I want to base by query.
2) I will be retrieving data from two tables.
3) I tried a simple query first (which worked fine):
select id, max(dateColumn) from tabelname group by id
The problem is that I want additional columns from each table that shouldn't be aggregated, but sql server says I need aggregate functions for all of the other columns. Do I have to run a query and put the results into a temp table, and then run a second query against the actual data to retrieve the records I want, or is there a magic generic aggregate function I can use on the other fields?
I am of the current opinion that SQL sucks.
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Wow. Your question well exceeds what I know about SQL.
I've never used temp tables, I very rarely use nested selects.
I'm still learning, mostly by reading the SQL forum.
Did you try the TOP 1 ... ORDER BY ... approach? that one typically works for me.
John Simmons / outlaw programmer wrote: I am of the current opinion that SQL sucks.
I'm afraid that will never change. At best, one gets used to it.
Suggestion: post your exact SQL statement, and wait for Mycroft to comment on it. He's probably the most knowledgeable around here.
|
|
|
|
|
I had to do something like this:
create temp table
select into temp table using group by
select from permanent AD temp table for desired result
Here's the whole this
if object_id('tempdb..#DUAL')is not null
DROP TABLE #DUAL
create table #DUAL
(
ID int,
datevalue datetime
)
insert into #DUAL (ID, datevalue)
select id, max(datevalue)
from metrics
group by ID
SELECT
a.ID
,a.itemType
,a.Title
,a.Description
,m.DateValue
FROM tableA a, metrics m, #Dual d
WHERE a.id = d.id AND a.ID = m.ID AND m.datevalue = d.datevalue
drop table #DUAL
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
SELECT a.Id,
a.ItemType,
a.Title,
a.Description,
m.DateValue
FROM TableA a
LEFT JOIN
(
SELECT Id,
MAX(DateValue) as DateValue
FROM Metrics
GROUP BY Id
) m ON m.Id = a.Id ;
If you only want Ids that have metrics then you can remove the LEFT to make it an inner join.
|
|
|
|
|
Actually, an ID won't be in the tables unless it has a metric. I want the data from both table a and table b, but I want only the latest data for each ID.
I tried to adapt the code you posted, but It SME complains with <sarcasm> it's way to verbose <sarcasm> error message:
Incorrect syntax near the keyword 'ON'.
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Try running this;
if object_id('tempdb..#TableA')is not null
DROP TABLE #TableA
go
if object_id('tempdb..#Metrics')is not null
DROP TABLE #Metrics
go
create table #TableA
(
Id int,
ItemType char(1),
Title varchar(50),
Description varchar(100)
)
go
create table #Metrics
(
ID int,
TableAId int,
datevalue datetime
)
go
insert into #TableA (ID, ItemType, Title, Description) values (1, 'A', 'First Title', 'First Description');
insert into #TableA (ID, ItemType, Title, Description) values (2, 'A', 'Second Title', 'Second Description');
insert into #TableA (ID, ItemType, Title, Description) values (3, 'B', 'Third Title', 'Third Description');
go
insert into #Metrics (ID, TableAId, datevalue) values (1, 1, '2010-01-01');
insert into #Metrics (ID, TableAId, datevalue) values (2, 1, '2010-01-02');
insert into #Metrics (ID, TableAId, datevalue) values (3, 3, '2010-01-03');
insert into #Metrics (ID, TableAId, datevalue) values (4, 2, '2010-01-04');
insert into #Metrics (ID, TableAId, datevalue) values (5, 2, '2010-01-05');
insert into #Metrics (ID, TableAId, datevalue) values (6, 3, '2010-01-06');
insert into #Metrics (ID, TableAId, datevalue) values (7, 1, '2010-01-07');
insert into #Metrics (ID, TableAId, datevalue) values (8, 2, '2010-01-08');
insert into #Metrics (ID, TableAId, datevalue) values (9, 1, '2010-01-09');
insert into #Metrics (ID, TableAId, datevalue) values (10, 3, '2010-01-10');
insert into #Metrics (ID, TableAId, datevalue) values (11, 3, '2010-01-11');
insert into #Metrics (ID, TableAId, datevalue) values (12, 1, '2010-01-12');
go
SELECT
a.ID
,a.itemType
,a.Title
,a.Description
,m.DateValue
FROM #TableA a
left join
(
select tableaid,
MAX(datevalue) as datevalue
from #Metrics
group by tableaid
) m on m.tableaid = a.id
go
drop table #Metrics
go
drop table #TableA
go
|
|
|
|
|
Hmmm... that ran just fine.
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Can you post the code that you have tried that gives the syntax error.
|
|
|
|
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ArticleMetrics](
[ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Votes] [int] NOT NULL,
[Bookmarks] [int] NOT NULL,
[Downloads] [int] NOT NULL,
[Popularity] [decimal](5, 2) NOT NULL,
[Rating] [decimal](5, 2) NOT NULL,
[LastUpdated] [smalldatetime] NOT NULL,
[Views] [int] NOT NULL,
[DateScraped] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ArticleMetrics] WITH CHECK ADD CONSTRAINT [FK_ArticleMetrics_Articles] FOREIGN KEY([ArticleID])
REFERENCES [dbo].[Articles] ([ArticleID])
GO
ALTER TABLE [dbo].[ArticleMetrics] CHECK CONSTRAINT [FK_ArticleMetrics_Articles]
USE [CPAM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Articles](
[ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ArticleType] [int] NOT NULL,
[Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DatePosted] [smalldatetime] NOT NULL,
[Active] [bit] NOT NULL,
[LastScraped] [datetime] NOT NULL,
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[ArticleID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Essentially, I want to combine the appropriate rows but I only want one record returned per ArticleID that represents the LAST record that was added (determined by MAX(ArticleMetrics.DateScraped)).
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I think that this is what you need;
SELECT a.*,
lm.*
FROM dbo.Articles AS a
CROSS APPLY
(SELECT TOP 1 m.*
FROM dbo.ArticleMetrics AS m
WHERE m.ArticleId = a.ArticleId
ORDER BY m.DateScraped DESC) AS lm
I would also add a compound primary key to ArticleMetrics on ArticleId, DateScraped.
|
|
|
|
|
But doesn't a primary key have to be unique? If that's the case, I can't make the DateScraped column a primary key.
BTW, I've never even heard of "Cross Apply" before.
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Yes PKs do have to be unique, but I was assuming that the combination of ArticleId and DateScrapped would be unique so you could include them in a compound primary key. Otherwise, you should add a identity field to uniquely identify each row.
Cross Apply and Outer Apply came in with Sql Server 2005. They are great for situations like 'Show me the last 5 orders per customer' that were very difficult before with ansi sql.
|
|
|
|
|
I simply can't get that to work. T-SQL is being as touchy as a ex-wife seeking more child support.
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Now how is the SQL code shown as the solution different from the one shown in your last post above?
I'm referring to:
select id, max(dateColumn) from tabelname group by id
|
|
|
|
|
Because I was trying to do it with a single SELECT statement, which is apparently impossible. You can't have non-aggregated columns in a group by select statement (and if I'm wrong about that, I hope someone can rebut that conclusion).
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I have two tables with a relationship set on a given column (the related column names are the same in each table, and both tables have that column set as the primary key).
Do I still have to do a join to get data from both tables, or does the configured relationship magically enable it?
Here's what I have so far (I actually list the individual columns I want to retrieve, but for the sake of brevity, I'm using "SELECT *". Also, The ID column is the primary key and the column on which the tables are related.
SELECT *
FROM TableA a, TableB b
WHERE a.ID = b.ID
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
i want to tell you that is the relationship ypu set is not relationship but
it is a constraint but the where statment "WHERE a.ID = b.ID" make the relationship or join statment
|
|
|
|
|